Oracle - Big Table Joins with Nested Loop

Hi

In this entry  i am gonna show faster execution sample for big data queries.

Environment: Oracle 11g


First we have the following SQL sample ( try to join 50M vs 88M data  which takes  7 minutes to return with parallel hint  )

select  /*+parallel( m1 , 12)  parallel( s1 12) */  *
from
test_table1  m1 ,    --50 million rows
test_table2 s1         --88 million rows
where
m1.col1=s1.col2   --Return Time : 7minutes

By Default optimizer choose HASH_JOIN which causes long execution time

Plan
SELECT STATEMENT ALL_ROWS Cost: 11 M Bytes: 268092 G Cardinality: 90 G
	3 HASH JOIN Cost: 11 M Bytes: 268092 G Cardinality: 90 G
		1 TABLE ACCESS FULL TABLE TEST_TABLE2 Cost: 474 K Bytes: 109 G Cardinality: 97 M
		2 TABLE ACCESS FULL TABLE TEST_TABLE1 Cost: 446 K Bytes: 114 G Cardinality: 61 M

To perform better first we will create an index  over join column (COL1)  in our smaller table.( TABLE1)

Then over same statement  , we add USE_NL hint  and we got the result as follows:

select   /*+ USE_NL( m1, s1)*/  *
from
test_table1  m1 ,
test_table2 s1
where
m1.col1=s1.col2   --   Return Time: 421 MSEC  (Much faster !! )

Plan
SELECT STATEMENT ALL_ROWS Cost: 72 G Bytes: 268092 G Cardinality: 90 G
	4 TABLE ACCESS BY INDEX ROWID TABLE TEST_TABLE1 Cost: 1 K Bytes: 2 M Cardinality: 923
		3 NESTED LOOPS Cost: 72 G Bytes: 268092 G Cardinality: 90 G
			1 TABLE ACCESS FULL TABLE TEST_TABLE2 Cost: 474 K Bytes: 109 G Cardinality: 97 M
			2 INDEX RANGE SCAN INDEX X_INDEX Cost: 2 Cardinality: 1 K