11g sort merge join

测试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 <, <=, >, or >=.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----------         2         1         5         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,t2 where t1.id>t2.id;Execution Plan----------------------------------------------------------Plan hash value: 1335671014------------------------------------------------------------------------------| 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