sort merge join,hash join,netsloop join

                                                Join Operations? SORT-MERGE JOIN– Sorts tables on the join key and then merges them together– Sorts are expensive? NESTED LOOPS– Retrieves a row from one table and finds the corresponding rows in the other table– Usually best for small numbers of rows? HASH JOIN– Build hash table in memory for smaller row source– Hash larger row source– Probe in-memory hash table for matches– Hash joins are only considered by the CBOSORT-MERGE JOIN根据关联列排序2个表,然后再进行merge操作当然排序是很昂贵的无驱动表概念支持不等值连接嵌套循环:Oracle从较小结果集(驱动表/外部表)中读取一行，然后和较大结果集(被探查表/内部表)中的所有数据逐条进行比较(嵌套循环可以用于非等值连接)，如果符合规则，就放入结果集中，然后取较小结果集的下一条数据继续进行循环，直到结束。嵌套循环只适合输出少量结果集或者是用于快速输出结果集。HASH JOINHASH JOIN 选择小表做驱动表，小表指的不是表的行数，而是指的是 行数*列宽度HASH JOIN只能用于等值连接把小的结果集(驱动表)来创建hash table然后 HASH 大的结果集探测内存里的Hash表来匹配:实验SQL:SELECT  a.cur_code,b.int_sub_code,b.plan_int_sub_code, a.acct_bran_code , sum(a.payable_int_amt)  as amt   FROM comr_intdist a,savc_buscode b   WHERE a.acct_flag = '1'   AND a.bus_code = b.bus_code   and a.LAST_MODI_DATE = '2013-12-31'   AND ((a.term = b.term   AND rtrim(a.sub_code) =rtrim(b.sub_code))   or   a.bus_code=77 )   GROUP BY a.cur_code,b.int_sub_code, b.plan_int_sub_code,a.acct_bran_code;SORT-MERGE JOIN：Execution Plan----------------------------------------------------------Plan hash value: 3181909998----------------------------------------------------------------------------------------------| Id  | Operation             | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT      |              |  8117 |   539K|       | 20129   (2)| 00:04:02 ||   1 |  HASH GROUP BY        |              |  8117 |   539K|       | 20129   (2)| 00:04:02 ||   2 |   MERGE JOIN          |              |  8117 |   539K|       | 20127   (2)| 00:04:02 ||   3 |    SORT JOIN          |              |   409 | 10634 |       |     6  (17)| 00:00:01 ||   4 |     TABLE ACCESS FULL | SAVC_BUSCODE |   409 | 10634 |       |     5   (0)| 00:00:01 ||*  5 |    FILTER             |              |       |       |       |            |          ||*  6 |     SORT JOIN         |              |  1136K|    45M|   139M| 20077   (2)| 00:04:01 ||*  7 |      TABLE ACCESS FULL| COMR_INTDIST |  1136K|    45M|       |  7704   (2)| 00:01:33 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   5 - filter("A"."TERM"="B"."TERM" AND RTRIM("A"."SUB_CODE")=RTRIM("B"."SUB_CODE")              OR "A"."BUS_CODE"=77)   6 - access("A"."BUS_CODE"="B"."BUS_CODE")       filter("A"."BUS_CODE"="B"."BUS_CODE")   7 - filter("A"."ACCT_FLAG"='1' AND "A"."LAST_MODI_DATE"=TO_DATE(' 2013-12-31              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))Statistics----------------------------------------------------------          1  recursive calls          2  db block gets      27016  consistent gets      27000  physical reads          0  redo size      30923  bytes sent via SQL*Net to client       1147  bytes received via SQL*Net from client         59  SQL*Net roundtrips to/from client          2  sorts (memory)          0  sorts (disk)        860  rows processed查看表的访问次数；Plan hash value: 3181909998------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |------------------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT      |              |      1 |        |    860 |00:00:07.55 |   27018 |  26880 |       |       |          ||   1 |  HASH GROUP BY        |              |      1 |   8117 |    860 |00:00:07.55 |   27018 |  26880 |   835K|   835K| 1454K (0)||   2 |   MERGE JOIN          |              |      1 |   8117 |   1131K|00:00:07.01 |   27018 |  26880 |       |       |          ||   3 |    SORT JOIN          |              |      1 |    409 |    409 |00:00:00.01 |      21 |      0 | 55296 | 55296 |49152  (0)||   4 |     TABLE ACCESS FULL | SAVC_BUSCODE |      1 |    409 |    409 |00:00:00.01 |      21 |      0 |       |       |          ||*  5 |    FILTER             |              |    409 |        |   1131K|00:00:06.65 |   26997 |  26880 |       |       |          ||*  6 |     SORT JOIN         |              |    409 |   1136K|   5241K|00:00:04.55 |   26997 |  26880 |    66M|  2830K|   59M (0)||*  7 |      TABLE ACCESS FULL| COMR_INTDIST |      1 |   1136K|   1138K|00:00:01.04 |   26997 |  26880 |       |       |          |------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   5 - filter((("A"."TERM"="B"."TERM" AND RTRIM("A"."SUB_CODE")=RTRIM("B"."SUB_CODE")) OR "A"."BUS_CODE"=77))   6 - access("A"."BUS_CODE"="B"."BUS_CODE")       filter("A"."BUS_CODE"="B"."BUS_CODE")   7 - filter(("A"."ACCT_FLAG"='1' AND "A"."LAST_MODI_DATE"=TO_DATE(' 2013-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))34 rows selected.嵌套循环:Execution Plan----------------------------------------------------------Plan hash value: 2053094138---------------------------------------------------------------------------------------------------| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT              |                   |  8117 |   539K|  2283K  (1)| 07:36:37 ||   1 |  HASH GROUP BY                |                   |  8117 |   539K|  2283K  (1)| 07:36:37 ||   2 |   NESTED LOOPS                |                   |       |       |            |          ||   3 |    NESTED LOOPS               |                   |  8117 |   539K|  2283K  (1)| 07:36:37 ||*  4 |     TABLE ACCESS FULL         | COMR_INTDIST      |  1136K|    45M|  7704   (2)| 00:01:33 ||*  5 |     INDEX RANGE SCAN          | SAVC_BUSCODE_IDX1 |     5 |       |     1   (0)| 00:00:01 ||*  6 |    TABLE ACCESS BY INDEX ROWID| SAVC_BUSCODE      |     1 |    26 |     2   (0)| 00:00:01 |---------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   4 - filter("A"."ACCT_FLAG"='1' AND "A"."LAST_MODI_DATE"=TO_DATE(' 2013-12-31 00:00:00',              'syyyy-mm-dd hh24:mi:ss'))   5 - access("A"."BUS_CODE"="B"."BUS_CODE")   6 - filter("A"."TERM"="B"."TERM" AND RTRIM("A"."SUB_CODE")=RTRIM("B"."SUB_CODE") OR              "A"."BUS_CODE"=77)Plan hash value: 2053094138-------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |-------------------------------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT              |                   |      1 |        |    860 |00:00:16.87 |     290K|  26880 |       |       |          ||   1 |  HASH GROUP BY                |                   |      1 |   8117 |    860 |00:00:16.87 |     290K|  26880 |   835K|   835K| 1456K (0)||   2 |   NESTED LOOPS                |                   |      1 |        |   1131K|00:00:15.90 |     290K|  26880 |       |       |          ||   3 |    NESTED LOOPS               |                   |      1 |   8117 |   5241K|00:00:06.74 |   27132 |  26880 |       |       |          ||*  4 |     TABLE ACCESS FULL         | COMR_INTDIST      |      1 |   1136K|   1138K|00:00:00.88 |   26997 |  26880 |       |       |          ||*  5 |     INDEX RANGE SCAN          | SAVC_BUSCODE_IDX1 |   1138K|      5 |   5241K|00:00:03.59 |     135 |      0 |       |       |          ||*  6 |    TABLE ACCESS BY INDEX ROWID| SAVC_BUSCODE      |   5241K|      1 |   1131K|00:00:06.08 |     262K|      0 |       |       |          |-------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   4 - filter(("A"."ACCT_FLAG"='1' AND "A"."LAST_MODI_DATE"=TO_DATE(' 2013-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))   5 - access("A"."BUS_CODE"="B"."BUS_CODE")   6 - filter((("A"."TERM"="B"."TERM" AND RTRIM("A"."SUB_CODE")=RTRIM("B"."SUB_CODE")) OR "A"."BUS_CODE"=77))32 rows selected.HASH JOIN:Execution Plan----------------------------------------------------------Plan hash value: 4188551662------------------------------------------------------------------------------------| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |              |  8117 |   539K|  7768   (3)| 00:01:34 ||   1 |  HASH GROUP BY      |              |  8117 |   539K|  7768   (3)| 00:01:34 ||*  2 |   HASH JOIN         |              |  8117 |   539K|  7767   (3)| 00:01:34 ||   3 |    TABLE ACCESS FULL| SAVC_BUSCODE |   409 | 10634 |     5   (0)| 00:00:01 ||*  4 |    TABLE ACCESS FULL| COMR_INTDIST |  1136K|    45M|  7704   (2)| 00:01:33 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("A"."BUS_CODE"="B"."BUS_CODE")       filter("A"."TERM"="B"."TERM" AND              RTRIM("A"."SUB_CODE")=RTRIM("B"."SUB_CODE") OR "A"."BUS_CODE"=77)   4 - filter("A"."ACCT_FLAG"='1' AND "A"."LAST_MODI_DATE"=TO_DATE('              2013-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))Statistics----------------------------------------------------------          1  recursive calls          2  db block gets      27016  consistent gets      26880  physical reads          0  redo size      37887  bytes sent via SQL*Net to client       1147  bytes received via SQL*Net from client         59  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)        860  rows processedPlan hash value: 4188551662----------------------------------------------------------------------------------------------------------------------------------| Id  | Operation           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |----------------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |              |      1 |        |    860 |00:00:05.11 |   27018 |  26880 |       |       |          ||   1 |  HASH GROUP BY      |              |      1 |   8117 |    860 |00:00:05.11 |   27018 |  26880 |   835K|   835K| 1456K (0)||*  2 |   HASH JOIN         |              |      1 |   8117 |   1131K|00:00:04.26 |   27018 |  26880 |   947K|   947K| 1257K (0)||   3 |    TABLE ACCESS FULL| SAVC_BUSCODE |      1 |    409 |    409 |00:00:00.01 |      21 |      0 |       |       |          ||*  4 |    TABLE ACCESS FULL| COMR_INTDIST |      1 |   1136K|   1138K|00:00:00.69 |   26997 |  26880 |       |       |          |----------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("A"."BUS_CODE"="B"."BUS_CODE")       filter((("A"."TERM"="B"."TERM" AND RTRIM("A"."SUB_CODE")=RTRIM("B"."SUB_CODE")) OR "A"."BUS_CODE"=77))   4 - filter(("A"."ACCT_FLAG"='1' AND "A"."LAST_MODI_DATE"=TO_DATE(' 2013-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))30 rows selected.