11g sort merge join

Test sort merge join

When the Optimizer Considers Sort Merge JoinsA hash join requires one hash table and one probe of this table, whereas a sort merge join requires two sorts.The optimizer may choose a sort merge join over a hash join for joining large amounts of data when any of the following conditions is true:The join condition between two tables is not an equijoin, that is, uses an inequality condition such as = .In contrast to sort merges, hash joins require an equality condition.Because of sorts required by other operations, the optimizer finds it cheaper to use a sort merge.If an index exists, then the database can avoid sorting the first data set. However, the database always sorts the second data set, regardless of indexes.SQL > create table T1 (id number); Table created.SQL > create table T2 (id number); Table created.SQL > insert into T1 values (2); 1 row created.SQL > insert into T1 values (1); 1 row created.SQL > insert into T1 values (5); 1 row created.SQL > insert into T1 values (3); 1 row created.SQL > commit;SQL > select * from T1 ID- 215 3SQL > insert into T2 values (11); 1 row created.SQL > insert into T2 values (9); 1 row created.SQL > insert into T2 values (1); 1 row created.SQL > commit;SQL > select * from T2 ID- 11 9 1SQL > SQL > exec dbms_stats.gather_table_stats ('SYS','T1'); PL/SQL procedure successfully completed.SQL > exec dbms_stats.gather_table_stats (' SYS','T2'); PL/SQL procedure successfully completed.SQL > set autot offSQL > create index ind_id on T1 (Id); Index created.SQL > set autot traceonlySQL > select * from T1 Id T2 where t1.id > t2.id Execution Plan---Plan hash value: 1335671014 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 2 | 12 | 4 (25) | 00:00:01 | | 1 | MERGE JOIN | | 2 | 12 | 4 (25) | 00:00:01 | | 2 | SORT JOIN | | 4 | 12 | 1 (0) | 00:00:01 | 3 | INDEX FULL SCAN | IND_ID | 4 | 12 | 1 (0) | 00:00:01 | | * 4 | SORT JOIN | | 3 | 9 | 3 (34) | 00:00:01 | 5 | TABLE ACCESS FULL | T2 | 3 | 9 | 2 (0) | 00:00:01 |- -Predicate Information (identified by operation id):-4-access (INTERNAL _ FUNCTION ("T1". "ID") > INTERNAL_FUNCTION ("T2". "ID") filter (INTERNAL_FUNCTION ("T1". "ID") > INTERNAL_FUNCTION ("T2". "ID") Statistics--- 1 recursive Calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 652 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 3 rows processed