[20180705]關於hash join 2.txt

lfree發表於2018-07-05
[20180705]關於hash join 2.txt

--//昨天最佳化sql語句,執行計劃hash join right sna,加入一個約束設定XX欄位not null,邏輯讀從上萬下降到50.
--//關於hash join派生的執行計劃,而且hash join還在外連線時支援右關聯,特別是11g,加入NULL-AWARW/Single Null-Aware的判斷,許
--//多我自己很混亂,做一點總結.

--//NA =>  Null-Aware.
--//SNA => Single Null-Aware

1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t1 as select level id   ,'t1'||to_char(level) name from dual connect by level<=4;
create table t2 as select level+1 id ,'t2'||to_char(level) name from dual connect by level<=4;
insert into t1 values (null,'t1null');
insert into t2 values (null,'t2null');
commit ;
--//分析略.

2.執行計劃包含hash join:
SCOTT@test01p> select * from t1,t2 where t1.id=t2.id;
        ID NAME                         ID NAME
---------- -------------------- ---------- --------------------
         2 t12                           2 t21
         3 t13                           3 t22
         4 t14                           4 t23

Plan hash value: 1838229974
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     8 (100)|          |      3 |00:00:00.01 |      15 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |      4 |    64 |     8   (0)| 00:00:01 |      3 |00:00:00.01 |      15 |  1888K|  1888K| 1063K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      4 |    32 |     4   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |       |       |          |
|*  3 |   TABLE ACCESS FULL| T2   |      1 |      4 |    32 |     4   (0)| 00:00:01 |      4 |00:00:00.01 |       8 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

--//如果改變連線順序,加入提示:
SCOTT@book> select /*+ leading(t2 t1) */ * from t1,t2 where t1.id=t2.id;
        ID NAME                         ID NAME
---------- -------------------- ---------- --------------------
         2 t12                           2 t21
         3 t13                           3 t22
         4 t14                           4 t23

Plan hash value: 2959412835
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     7 (100)|          |      3 |00:00:00.01 |       9 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |      4 |    56 |     7   (0)| 00:00:01 |      3 |00:00:00.01 |       9 |  1888K|  1888K|  830K (0)|
|   2 |   TABLE ACCESS FULL| T2   |      1 |      4 |    28 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       2 |       |       |          |
|*  3 |   TABLE ACCESS FULL| T1   |      1 |      4 |    28 |     4   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

3.執行計劃包含HASH JOIN OUTER/HASH JOIN RIGHT OUTER:
SCOTT@book> select * from t1,t2 where t1.id=t2.id(+);
        ID NAME                         ID NAME
---------- -------------------- ---------- --------------------
         2 t12                           2 t21
         3 t13                           3 t22
         4 t14                           4 t23
           t1null
         1 t11

Plan hash value: 1823443478
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     7 (100)|          |      5 |00:00:00.01 |       9 |       |       |          |
|*  1 |  HASH JOIN OUTER   |      |      1 |      5 |    84 |     7   (0)| 00:00:01 |      5 |00:00:00.01 |       9 |  1888K|  1888K|  935K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |      5 |    42 |     4   (0)| 00:00:01 |      5 |00:00:00.01 |       6 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |      4 |    28 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       3 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

--// 透過改變連線順序,HASH JOIN OUTER:
SCOTT@book> select /*+ SWAP_JOIN_INPUTS(t2) */ * from t1,t2 where t1.id=t2.id(+);
        ID NAME                         ID NAME
---------- -------------------- ---------- --------------------
         1 t11
         2 t12                           2 t21
         3 t13                           3 t22
         4 t14                           4 t23
           t1null

Plan hash value: 312430291
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |       |     8 (100)|          |      5 |00:00:00.01 |      15 |       |       |          |
|*  1 |  HASH JOIN RIGHT OUTER|      |      1 |      5 |    80 |     8   (0)| 00:00:01 |      5 |00:00:00.01 |      15 |  1888K|  1888K|  926K (0)|
|*  2 |   TABLE ACCESS FULL   | T2   |      1 |      4 |    32 |     4   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL   | T1   |      1 |      5 |    40 |     4   (0)| 00:00:01 |      5 |00:00:00.01 |       8 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------

--//在nested loop,merge join,hash join連線中,僅僅hash join在外連線中支援right連線模式,其它一般不提都是left連線.
--//nested loop 演算法根本不可能支援right連線模式.
--//而merge join理論可以支援,但是因為不管如何metge join 兩邊都要排序,完全沒有必要在支援right連線模式.(注這個是我個人的理解,也許不對)
--//而hash join如果右關聯表生成hash表小,完全可能右關聯表作為驅動表.

4.執行計劃包含hash join anti/hash join right anti:
SCOTT@book> select * from t1 where id not in (select id from t2 where id is not null) and id is not null;
        ID NAME
---------- --------------------
         1 t11

--//我現在查詢2個表的id is not null.

Plan hash value: 2706079091
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     8 (100)|          |      1 |00:00:00.01 |      12 |       |       |          |
|*  1 |  HASH JOIN ANTI    |      |      1 |      1 |    11 |     8   (0)| 00:00:01 |      1 |00:00:00.01 |      12 |  1888K|  1888K|  978K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      4 |    32 |     4   (0)| 00:00:01 |      4 |00:00:00.01 |       6 |       |       |          |
|*  3 |   TABLE ACCESS FULL| T2   |      1 |      4 |    12 |     4   (0)| 00:00:01 |      4 |00:00:00.01 |       6 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

--//如何實現交換順序呢?
SCOTT@book> select * from t1 where id not in (select /*+ SWAP_JOIN_INPUTS(t2) */ id from t2 where id is not null) and id is not null;
        ID NAME
---------- --------------------
         1 t11

Plan hash value: 629543484
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |       |     8 (100)|          |      1 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN RIGHT ANTI|      |      1 |      1 |    11 |     8   (0)| 00:00:01 |      1 |00:00:00.01 |      13 |  2440K|  2440K|  855K (0)|
|*  2 |   TABLE ACCESS FULL  | T2   |      1 |      4 |    12 |     4   (0)| 00:00:01 |      4 |00:00:00.01 |       6 |       |       |          |
|*  3 |   TABLE ACCESS FULL  | T1   |      1 |      4 |    32 |     4   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------

5.執行計劃包含hash join semi/hash join right semi:
SCOTT@book> select * from t1 where id  in (select id from t2 where id is not null) and id is not null;
        ID NAME
---------- --------------------
         2 t12
         3 t13
         4 t14

Plan hash value: 1713220790
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     8 (100)|          |      3 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN SEMI    |      |      1 |      3 |    33 |     8   (0)| 00:00:01 |      3 |00:00:00.01 |      13 |  1888K|  1888K|  935K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      4 |    32 |     4   (0)| 00:00:01 |      4 |00:00:00.01 |       6 |       |       |          |
|*  3 |   TABLE ACCESS FULL| T2   |      1 |      4 |    12 |     4   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

--//改變交換順序呢?
SCOTT@book> select * from t1 where id  in (select /*+ SWAP_JOIN_INPUTS(t2) */ id from t2 where id is not null) and id is not null;
        ID NAME
---------- --------------------
         2 t12
         3 t13
         4 t14

Plan hash value: 1275841967
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |       |     8 (100)|          |      3 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN RIGHT SEMI|      |      1 |      3 |    33 |     8   (0)| 00:00:01 |      3 |00:00:00.01 |      13 |  2440K|  2440K|  814K (0)|
|*  2 |   TABLE ACCESS FULL  | T2   |      1 |      4 |    12 |     4   (0)| 00:00:01 |      4 |00:00:00.01 |       6 |       |       |          |
|*  3 |   TABLE ACCESS FULL  | T1   |      1 |      4 |    32 |     4   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------

5.執行計劃包含HASH JOIN FULL OUTER:
SCOTT@book> select * from t1 full outer join t2  on  t1.id=t2.id;
        ID NAME                         ID NAME
---------- -------------------- ---------- --------------------
         2 t12                           2 t21
         3 t13                           3 t22
         4 t14                           4 t23
                                         5 t24
                                           t2null
           t1null
         1 t11

7 rows selected.

Plan hash value: 53297166
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |       |     8 (100)|          |      7 |00:00:00.01 |      13 |       |       |          |
|   1 |  VIEW                 | VW_FOJ_0 |      1 |      5 |   360 |     8   (0)| 00:00:01 |      7 |00:00:00.01 |      13 |       |       |          |
|*  2 |   HASH JOIN FULL OUTER|          |      1 |      5 |    80 |     8   (0)| 00:00:01 |      7 |00:00:00.01 |      13 |  1888K|  1888K|  936K (0)|
|   3 |    TABLE ACCESS FULL  | T1       |      1 |      5 |    40 |     4   (0)| 00:00:01 |      5 |00:00:00.01 |       6 |       |       |          |
|   4 |    TABLE ACCESS FULL  | T2       |      1 |      5 |    40 |     4   (0)| 00:00:01 |      5 |00:00:00.01 |       7 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------
--//交換連線順序略.

6.執行計劃包含hash join anti NA/hash join right anti NA:
SCOTT@book> select * from t1 where id not in (select id from t2 ) ;
no rows selected

Plan hash value: 1275484728
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     8 (100)|          |      0 |00:00:00.01 |      10 |       |       |          |
|*  1 |  HASH JOIN ANTI NA |      |      1 |      2 |    22 |     8   (0)| 00:00:01 |      0 |00:00:00.01 |      10 |  1888K|  1888K|  936K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |      5 |    40 |     4   (0)| 00:00:01 |      5 |00:00:00.01 |       6 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |      5 |    15 |     4   (0)| 00:00:01 |      5 |00:00:00.01 |       4 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

--//NA表示Null-Aware,我開始以為2邊都要判斷id是否存在NULL值.實際上如果執行如下:
SCOTT@book> select * from t1 where id not in (select id from t2 ) and id is not null;
no rows selected

Plan hash value: 1275484728
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     8 (100)|          |      0 |00:00:00.01 |      10 |       |       |          |
|*  1 |  HASH JOIN ANTI NA |      |      1 |      1 |    11 |     8   (0)| 00:00:01 |      0 |00:00:00.01 |      10 |  1888K|  1888K|  936K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      4 |    32 |     4   (0)| 00:00:01 |      4 |00:00:00.01 |       6 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |      5 |    15 |     4   (0)| 00:00:01 |      5 |00:00:00.01 |       4 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

--//說明實際上NA僅僅判斷右關聯表查詢連線條件是否為NULL.
--//另外這種查詢非常容易引起歧義性,許多開發包括我,以前一直以為至少返回id=1的記錄.
--//實際上正是T2表裡面id存在空值導致沒有結果.即使寫成如下,結果也一樣:
select * from t1 where id not in (select id from t2 ) and id is not null;
--//另外注意僅僅not in,not exists在執行計劃中才存在NA,SNA.

--//如何交換:
select * from t1 where id not in (select /*+ SWAP_JOIN_INPUTS(t2) */ id from t2 ) ;
--//以上這樣寫不行.要寫成如下(注我使用透過獲得outlined的執行計劃),猜測這樣加提示的:
SCOTT@book> select /*+ SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "T2"@"SEL$2") */ * from t1 where id not in (select id from t2 ) ;
no rows selected

Plan hash value: 2739594415
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |        |       |     8 (100)|          |      0 |00:00:00.01 |       4 |       |       |          |
|*  1 |  HASH JOIN RIGHT ANTI NA|      |      1 |      2 |    22 |     8   (0)| 00:00:01 |      0 |00:00:00.01 |       4 |  1817K|  1817K|  841K (0)|
|   2 |   TABLE ACCESS FULL     | T2   |      1 |      5 |    15 |     4   (0)| 00:00:01 |      5 |00:00:00.01 |       4 |       |       |          |
|   3 |   TABLE ACCESS FULL     | T1   |      0 |      5 |    40 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------
--//這裡注意一個細節,T1實際上根本沒有做全表掃描,邏輯讀是0,starts=0.
--//插曲:補充測試,給T2增加一些測試資料:
insert into t2  select rownum+4 ,'t2'||to_char(rownum+4) from dual connect by level<=10000;
commit;

SCOTT@book> set autot traceonly
SCOTT@book> select count(*) from t2;
Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    10   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T2   | 10005 |    10   (0)| 00:00:01 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SCOTT@book> set autot off
--//可以發現全表掃描T1,邏輯讀是30.

SCOTT@book> select * from t1 where id not in (select id from t2 ) and id is not null;
no rows selected

Plan hash value: 1275484728
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |    14 (100)|          |      0 |00:00:00.01 |      11 |       |       |          |
|*  1 |  HASH JOIN ANTI NA |      |      1 |      1 |    12 |    14   (0)| 00:00:01 |      0 |00:00:00.01 |      11 |  1888K|  1888K| 1008K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      4 |    32 |     4   (0)| 00:00:01 |      4 |00:00:00.01 |       6 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |  10005 | 40020 |    10   (0)| 00:00:01 |    459 |00:00:00.01 |       5 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------
--//你可以發現一個奇特現象,T2實際讀取459條記錄,buffers=5.並不是30,實際上因為前面已經存在id is null的記錄,這樣返回的結果集一定是沒有結果,這樣
--//停止了T2表的掃描.

SCOTT@book> column PARTITION_NAME noprint
SCOTT@book>  select * from dba_extents where segment_name='T2';
OWNER  SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ -------------------- ------------------ --------------- --------- ---------- ---------- ---------- ---------- ------------
SCOTT  T2                   TABLE              USERS                   0          4        552      65536          8            4
SCOTT  T2                   TABLE              USERS                   1          4        560      65536          8            4
SCOTT  T2                   TABLE              USERS                   2          4        672      65536          8            4
SCOTT  T2                   TABLE              USERS                   3          4        680      65536          8            4

SCOTT@book> select count(*) from t2 where DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) between 555 and 556;
  COUNT(*)
----------
       458
--//458,差1條與執行計劃統計.可以推測僅僅掃描讀取了資料塊555,556,557塊.

7.執行計劃包含hash join anti SNA/hash join right anti SNA:

SCOTT@book> select * from t1 where id not in (select id from t2 where id is not null) ;
        ID NAME
---------- --------------------
         1 t11

Plan hash value: 1270581391
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |    14 (100)|          |      1 |00:00:00.01 |      36 |       |       |          |
|*  1 |  HASH JOIN ANTI SNA|      |      1 |      2 |    24 |    14   (0)| 00:00:01 |      1 |00:00:00.01 |      36 |  1888K|  1888K| 1008K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |      5 |    40 |     4   (0)| 00:00:01 |      5 |00:00:00.01 |       6 |       |       |          |
|*  3 |   TABLE ACCESS FULL| T2   |      1 |  10004 | 40016 |    10   (0)| 00:00:01 |  10004 |00:00:00.01 |      30 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

--//注:當限制T2查詢條件id is not null,這樣查詢僅僅檢查T1表的id是否存在NULL.也就是SNA.
--//SNA 表示 Single Null-Aware ,也就是檢查左關聯表連線條件是否存在null.

--//EXECUTE SYS.DBMS_STATS.SET_TABLE_STATS (OWNNAME=>'SCOTT', TABNAME=>'T1', NUMBLKS=> 800000);
--//EXECUTE SYS.DBMS_STATS.SET_TABLE_STATS (OWNNAME=>'SCOTT', TABNAME=>'T1', NUMROWS=> 8000);

--//交換順序:
SCOTT@book> select /*+  SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "T2"@"SEL$2") */ * from t1 where id not in (select id from t2 where id is not null) ;
        ID NAME
---------- --------------------
         1 t11

Plan hash value: 1521920066
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |       |    14 (100)|          |      1 |00:00:00.01 |      37 |       |       |          |
|*  1 |  HASH JOIN RIGHT ANTI SNA|      |      1 |      2 |    24 |    14   (0)| 00:00:01 |      1 |00:00:00.01 |      37 |  2440K|  2440K| 1900K (0)|
|*  2 |   TABLE ACCESS FULL      | T2   |      1 |  10004 | 40016 |    10   (0)| 00:00:01 |  10004 |00:00:00.01 |      30 |       |       |          |
|   3 |   TABLE ACCESS FULL      | T1   |      1 |      5 |    40 |     4   (0)| 00:00:01 |      5 |00:00:00.01 |       7 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------

總結:
1.NA僅僅判斷右關聯表查詢連線條件是否為NULL.
2.SNA僅僅判斷左關聯表查詢連線條件是否為NULL.
3.注意NULL判斷,NULL的邏輯僅僅存在 NULL is NULL 是true,NULL is not NULL是false,其它NULL=NULL之類的都表示noknown.
4.僅僅hash join支援右連線模式.
5.注意僅僅not in,not exists在執行計劃中才存在NA,SNA.
6.個人建議,使用exists/not exists代替in/not in,大部分滿足業務與實際查詢的需要.
7.適當給欄位加入not null,規避一些全表掃描,因為全NULL值,在oracle中的常規索引不記錄,導致無法使用索引.
8.從以上測試,可以發現hash join派生許多操作方式:
HASH JOIN,HASH JOIN OUTER/HASH JOIN RIGHT OUTER,
HASH JOIN ANTI/HASH JOIN RIGHT ANTI,
HASH JOIN SEMI/HASH JOIN RIGHT SEMI,
HASH JOIN FULL OUTER,
HASH JOIN ANTI NA/HASH JOIN RIGHT ANTI NA,
HASH JOIN ANTI SNA/HASH JOIN RIGHT ANTI SNA.
9.以上完全是基於測試的猜測,不知道是否正確.

10.補充使用exists/not exists的測試:(12c)
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> select * from t1 where not exists (select id from t2 where t2.id=t1.id) ;
        ID NAME
---------- --------------------
           t1null
         1 t11

Plan hash value: 2706079091
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |    14 (100)|          |      2 |00:00:00.02 |      38 |       |       |          |
|*  1 |  HASH JOIN ANTI    |      |      1 |      2 |    24 |    14   (0)| 00:00:01 |      2 |00:00:00.02 |      38 |  1888K|  1888K| 1040K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |      5 |    40 |     4   (0)| 00:00:01 |      5 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |  10005 | 40020 |    10   (0)| 00:00:01 |  10005 |00:00:00.01 |      31 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------
--//注意條件是t2.id=t1.id,這樣T2.id是null的記錄被排除了.注意與使用not in結果不同:
SCOTT@test01p> select  * from t1 where id not in (select id from t2 where id is not null) ;
        ID NAME
---------- --------------------
         1 t11
--//如果使用exists這樣的效果.
SCOTT@test01p> select * from t1 where  not exists (select id from t2 where t2.id=t1.id) and id is not null;
        ID NAME
---------- --------------------
         1 t11

--//exists
SCOTT@test01p> select * from t1 where  exists (select id from t2 where t2.id=t1.id) ;
        ID NAME
---------- --------------------
         2 t12
         3 t13
         4 t14

Plan hash value: 1713220790
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |    14 (100)|          |      3 |00:00:00.01 |      39 |       |       |          |
|*  1 |  HASH JOIN SEMI    |      |      1 |      4 |    48 |    14   (0)| 00:00:01 |      3 |00:00:00.01 |      39 |  1888K|  1888K| 1040K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      4 |    32 |     4   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |  10005 | 40020 |    10   (0)| 00:00:01 |  10005 |00:00:00.01 |      32 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2157424/,如需轉載,請註明出處,否則將追究法律責任。

相關文章