[20180705]關於hash join 2.txt--//昨天優化sql語句,執行計劃hash join right sna,加入一個約束設置XX欄位not null,邏輯讀從上萬下降到50.--//關於hash join派生的執行計劃,而且hash join還在外連接時支持右關聯,特別是11g ...
[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 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
