[20140823]12c join convert連線轉換.txt
[20140823]12c join convert連線轉換.txt
--前面提高12c執行計劃的Partial Join Evaluation.現在看看12c join convert.
--連結:
1.建立測試環境:
SCOTT@test01p> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table t1 as select rownum id ,cast('testtest' as varchar2(10)) name from dual connect by level<=1e5;
create table t2 as select rownum id ,cast('testtest' as varchar2(10)) name from dual connect by level<=10;
create unique index pk_t1 on t1 (id);
alter table t1 add constraint pk_t1 primary key (id);
create unique index pk_t2 on t2 (id);
alter table t2 add constraint pk_t2 primary key (id);
execute dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);
execute dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);
SYS@test01p> @hide _convert_set_to_join
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
--------------------- ------------------------------------------- -------------- -------------- ------------
_convert_set_to_join enables conversion of set operator to join TRUE FALSE FALSE
--12.1.0.1版本_convert_set_to_join=false.
2.測試:
SCOTT@test01p> alter session set statistics_level=all;
Session altered.
SCOTT@test01p> select * from t1 intersect select * from t2;
ID NAME
--- ---------
1 testtest
2 testtest
3 testtest
4 testtest
5 testtest
6 testtest
7 testtest
8 testtest
9 testtest
10 testtest
10 rows selected.
SCOTT@test01p> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g5jv20wwdh1zz, child number 0
-------------------------------------
select * from t1 intersect select * from t2
Plan hash value: 1917753433
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 576 (100)| 10 |00:00:00.08 | 269 | | | |
| 1 | INTERSECTION | | 1 | | | 10 |00:00:00.08 | 269 | | | |
| 2 | SORT UNIQUE | | 1 | 100K| 573 (1)| 100K|00:00:00.07 | 266 | 5510K| 963K| 4897K (0)|
| 3 | TABLE ACCESS FULL| T1 | 1 | 100K| 77 (0)| 100K|00:00:00.01 | 266 | | | |
| 4 | SORT UNIQUE | | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL| T2 | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 3 | | | |
------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SET$1")
FULL(@"SEL$2" "T2"@"SEL$2")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
--可以發現執行出現INTERSECTION,與一些11g版本一樣.Buffers=269.
SCOTT@test01p> select /*+ SET_TO_JOIN(@"SET$1") */ * from t1 intersect select * from t2;
ID NAME
-- --------------------
7 testtest
8 testtest
2 testtest
5 testtest
1 testtest
3 testtest
6 testtest
10 testtest
4 testtest
9 testtest
--注意輸出的順序發生了一些"混亂",對比前面的輸出.
SCOTT@test01p> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7y6uhq1r2bhnr, child number 0
-------------------------------------
select /*+ SET_TO_JOIN(@"SET$1") */ * from t1 intersect select * from t2
Plan hash value: 847386728
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 13 (100)| 10 |00:00:00.01 | 13 | | | |
| 1 | HASH UNIQUE | | 1 | 10 | 13 (0)| 10 |00:00:00.01 | 13 | 1600K| 1600K| 979K (0)|
| 2 | NESTED LOOPS | | 1 | | | 10 |00:00:00.01 | 13 | | | |
| 3 | NESTED LOOPS | | 1 | 10 | 13 (0)| 10 |00:00:00.01 | 12 | | | |
| 4 | TABLE ACCESS FULL | T2 | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 3 | | | |
|* 5 | INDEX UNIQUE SCAN | PK_T1 | 10 | 1 | 0 (0)| 10 |00:00:00.01 | 9 | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 1 | 1 (0)| 10 |00:00:00.01 | 1 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$02B15F54")
MERGE(@"SEL$1")
MERGE(@"SEL$2")
OUTLINE(@"SET$09AAA538")
SET_TO_JOIN(@"SET$1")
~~~~~~~~~~~~~~~~~~~~~
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SET$1")
FULL(@"SEL$02B15F54" "T2"@"SEL$2")
INDEX(@"SEL$02B15F54" "T1"@"SEL$1" ("T1"."ID"))
LEADING(@"SEL$02B15F54" "T2"@"SEL$2" "T1"@"SEL$1")
USE_NL(@"SEL$02B15F54" "T1"@"SEL$1")
NLJ_BATCHING(@"SEL$02B15F54" "T1"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$02B15F54")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."ID"="T2"."ID")
6 - filter(SYS_OP_MAP_NONNULL("T1"."NAME")=SYS_OP_MAP_NONNULL("T2"."NAME"))
Note
-----
- this is an adaptive plan
--可以發現幾點變化:
1.執行計劃沒有出現INTERSECTION,而是轉化為join操作.
2.另外一個副作用這個執行計劃adaptive plan,後面再看看為什麼?
3.buffer=13,比原來大大減少.操作先掃描T2小表.在連線T1,邏輯讀自然大大減少.
4.看不明白,為什麼輸出的順序會發生變化.....?????也許出在執行計劃的HASH UNIQUE上.
3.使用提示看看:
SCOTT@test01p> select /*+ OPT_PARAM('_convert_set_to_join' 'true') */ * from t1 intersect select * from t2;
ID NAME
-- --------------------
7 testtest
8 testtest
2 testtest
5 testtest
1 testtest
3 testtest
6 testtest
10 testtest
4 testtest
9 testtest
10 rows selected.
--執行計劃與select /*+ SET_TO_JOIN(@"SET$1") */ * from t1 intersect select * from t2一樣,不再貼出.
4.修改引數測試:
--我使用的12.1.0.1.0版本._convert_set_to_join=false.設定為true看看.
SCOTT@test01p> alter session set "_convert_set_to_join"=true ;
Session altered.
SCOTT@test01p> select * from t1 intersect select * from t2;
ID NAME
-- --------------------
7 testtest
8 testtest
2 testtest
5 testtest
1 testtest
3 testtest
6 testtest
10 testtest
4 testtest
9 testtest
10 rows selected.
--執行計劃與select /*+ SET_TO_JOIN(@"SET$1") */ * from t1 intersect select * from t2一樣,不在貼出.
5.看看是否順序可以改變:
SCOTT@test01p> select * from V$SQL_HINT where name like '%USE%AGGREGATION%';
NAME SQL_FEATURE CLASS INVERSE TARGET_LEVEL PROPERTY VERSION VERSION_OUTLINE CON_ID
----------------------- -------------------- --------------------- ------------------------ ------------ ---------- ---------- --------------- ------
USE_HASH_AGGREGATION QKSFM_ALL USE_HASH_AGGREGATION NO_USE_HASH_AGGREGATION 2 0 10.2.0.1 10.2.0.5 0
NO_USE_HASH_AGGREGATION QKSFM_ALL USE_HASH_AGGREGATION USE_HASH_AGGREGATION 2 0 10.2.0.1 10.2.0.5 0
--猜測換成NO_USE_HASH_AGGREGATION看看.
select
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$02B15F54")
MERGE(@"SEL$1")
MERGE(@"SEL$2")
OUTLINE(@"SET$09AAA538")
SET_TO_JOIN(@"SET$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SET$1")
FULL(@"SEL$02B15F54" "T2"@"SEL$2")
INDEX(@"SEL$02B15F54" "T1"@"SEL$1" ("T1"."ID"))
LEADING(@"SEL$02B15F54" "T2"@"SEL$2" "T1"@"SEL$1")
USE_NL(@"SEL$02B15F54" "T1"@"SEL$1")
NLJ_BATCHING(@"SEL$02B15F54" "T1"@"SEL$1")
NO_USE_HASH_AGGREGATION(@"SEL$02B15F54")
END_OUTLINE_DATA
*/ * from t1 intersect select * from t2;
ID NAME
-- --------------------
1 testtest
2 testtest
3 testtest
4 testtest
5 testtest
6 testtest
7 testtest
8 testtest
9 testtest
10 testtest
10 rows selected.
SCOTT@test01p> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3frmt98v3kjsp, child number 0
-------------------------------------
select /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1') ALL_ROWS
OUTLINE_LEAF(@"SEL$02B15F54") MERGE(@"SEL$1")
MERGE(@"SEL$2") OUTLINE(@"SET$09AAA538")
SET_TO_JOIN(@"SET$1") OUTLINE(@"SEL$1") OUTLINE(@"SEL$2")
OUTLINE(@"SET$1") FULL(@"SEL$02B15F54" "T2"@"SEL$2")
INDEX(@"SEL$02B15F54" "T1"@"SEL$1" ("T1"."ID"))
LEADING(@"SEL$02B15F54" "T2"@"SEL$2" "T1"@"SEL$1")
USE_NL(@"SEL$02B15F54" "T1"@"SEL$1") NLJ_BATCHING(@"SEL$02B15F54"
"T1"@"SEL$1") NO_USE_HASH_AGGREGATION(@"SEL$02B15F54")
END_OUTLINE_DATA */ * from t1 intersect select * from t2
Plan hash value: 1438968633
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 13 (100)| 10 |00:00:00.01 | 13 | | | |
| 1 | SORT UNIQUE | | 1 | 8 | 13 (0)| 10 |00:00:00.01 | 13 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS | | 1 | | | 10 |00:00:00.01 | 13 | | | |
| 3 | NESTED LOOPS | | 1 | 10 | 13 (0)| 10 |00:00:00.01 | 12 | | | |
| 4 | TABLE ACCESS FULL | T2 | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 3 | | | |
|* 5 | INDEX UNIQUE SCAN | PK_T1 | 10 | 1 | 0 (0)| 10 |00:00:00.01 | 9 | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 1 | 1 (0)| 10 |00:00:00.01 | 1 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$02B15F54")
MERGE(@"SEL$1")
MERGE(@"SEL$2")
OUTLINE(@"SET$09AAA538")
SET_TO_JOIN(@"SET$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SET$1")
FULL(@"SEL$02B15F54" "T2"@"SEL$2")
INDEX(@"SEL$02B15F54" "T1"@"SEL$1" ("T1"."ID"))
LEADING(@"SEL$02B15F54" "T2"@"SEL$2" "T1"@"SEL$1")
USE_NL(@"SEL$02B15F54" "T1"@"SEL$1")
NLJ_BATCHING(@"SEL$02B15F54" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."ID"="T2"."ID")
6 - filter(SYS_OP_MAP_NONNULL("T1"."NAME")=SYS_OP_MAP_NONNULL("T2"."NAME"))
60 rows selected.
6.最後觀察為什麼出現adaptive plan.
--重新進入:
SCOTT@test01p> alter session set statistics_level=all;
Session altered.
select /*+ SET_TO_JOIN(@"SET$1") */ * from t1 intersect select * from t2;
SCOTT@test01p> @dpc '' outline,adaptive
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7y6uhq1r2bhnr, child number 2
-------------------------------------
select /*+ SET_TO_JOIN(@"SET$1") */ * from t1 intersect select * from t2
Plan hash value: 847386728
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 13 (100)| 10 |00:00:00.01 | 13 | | | |
| 1 | HASH UNIQUE | | 1 | 1 | 13 (0)| 10 |00:00:00.01 | 13 | 1600K| 1600K| 985K (0)|
|- * 2 | HASH JOIN | | 1 | 1 | 13 (0)| 10 |00:00:00.01 | 13 | 1079K| 1079K| |
| 3 | NESTED LOOPS | | 1 | | | 10 |00:00:00.01 | 13 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 13 (0)| 10 |00:00:00.01 | 12 | | | |
|- 5 | STATISTICS COLLECTOR | | 1 | | | 10 |00:00:00.01 | 3 | | | |
| 6 | TABLE ACCESS FULL | T2 | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 3 | | | |
| * 7 | INDEX UNIQUE SCAN | PK_T1 | 10 | 1 | 0 (0)| 10 |00:00:00.01 | 9 | | | |
| * 8 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 1 | 1 (0)| 10 |00:00:00.01 | 1 | | | |
|- 9 | TABLE ACCESS FULL | T1 | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$02B15F54")
MERGE(@"SEL$1")
MERGE(@"SEL$2")
OUTLINE(@"SET$09AAA538")
SET_TO_JOIN(@"SET$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SET$1")
FULL(@"SEL$02B15F54" "T2"@"SEL$2")
INDEX(@"SEL$02B15F54" "T1"@"SEL$1" ("T1"."ID"))
LEADING(@"SEL$02B15F54" "T2"@"SEL$2" "T1"@"SEL$1")
USE_NL(@"SEL$02B15F54" "T1"@"SEL$1")
NLJ_BATCHING(@"SEL$02B15F54" "T1"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$02B15F54")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID" AND SYS_OP_MAP_NONNULL("T1"."NAME")=SYS_OP_MAP_NONNULL("T2"."NAME"))
7 - access("T1"."ID"="T2"."ID")
Note
-----
- statistics feedback used for this statement
- this is an adaptive plan (rows marked '-' are inactive)
59 rows selected.
--對adaptive plan不是很熟悉,可以理解marked '-' are inactive.或者先生成的是這些帶-,實際執行時發生了改變.
7.再回頭看看原始的執行計劃:
SCOTT@test01p> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g5jv20wwdh1zz, child number 0
-------------------------------------
select * from t1 intersect select * from t2
Plan hash value: 1917753433
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 576 (100)| 10 |00:00:00.08 | 269 | | | |
| 1 | INTERSECTION | | 1 | | | 10 |00:00:00.08 | 269 | | | |
| 2 | SORT UNIQUE | | 1 | 100K| 573 (1)| 100K|00:00:00.07 | 266 | 5510K| 963K| 4897K (0)|
| 3 | TABLE ACCESS FULL| T1 | 1 | 100K| 77 (0)| 100K|00:00:00.01 | 266 | | | |
| 4 | SORT UNIQUE | | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL| T2 | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 3 | | | |
------------------------------------------------------------------------------------------------------------------------------
--即使我們建立每個表上都建立了主鍵(欄位id),而老的方法選擇的仍然是全表掃描,每條記錄肯定唯一,而在選擇sort unique有點多餘.
--即使寫成如下:
select id from t1 intersect select id from t2;
--執行計劃如下,依舊出現SORT UNIQUE.buffers=218 , 也不小.
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 365 (100)| 10 |00:00:00.10 | 218 | 2 | | | |
| 1 | INTERSECTION | | 1 | | | 10 |00:00:00.10 | 218 | 2 | | | |
| 2 | SORT UNIQUE | | 1 | 100K| 364 (1)| 100K|00:00:00.09 | 216 | 1 | 4588K| 893K| 4078K (0)|
| 3 | INDEX FAST FULL SCAN| PK_T1 | 1 | 100K| 58 (0)| 100K|00:00:00.04 | 216 | 1 | | | |
| 4 | SORT UNIQUE NOSORT | | 1 | 10 | 1 (0)| 10 |00:00:00.01 | 2 | 1 | | | |
| 5 | INDEX FULL SCAN | PK_T2 | 1 | 10 | 1 (0)| 10 |00:00:00.01 | 2 | 1 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------
--實際上寫成這樣,輸出結果一樣.
select id,name from t2 where (id,name) in ( select id,name from t1);
select id,name from t1 where (id,name) in ( select id,name from t2);
--僅僅貼出第二個執行計劃.實際上如果看Plan hash value是一樣的.
SCOTT@test01p> select id,name from t1 where (id,name) in ( select id,name from t2);
ID NAME
---------- --------------------
1 testtest
2 testtest
3 testtest
4 testtest
5 testtest
6 testtest
7 testtest
8 testtest
9 testtest
10 testtest
10 rows selected.
SCOTT@test01p> @dpc '' outline,adaptive
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID dnk2d8zxsk4gf, child number 0
-------------------------------------
select id,name from t1 where (id,name) in ( select id,name from t2)
Plan hash value: 4001747048
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 13 (100)| 10 |00:00:00.01 | 23 |
|- * 1 | HASH JOIN | | 1 | 10 | 13 (0)| 10 |00:00:00.01 | 23 |
| 2 | NESTED LOOPS | | 1 | | | 10 |00:00:00.01 | 23 |
| 3 | NESTED LOOPS | | 1 | 10 | 13 (0)| 10 |00:00:00.01 | 13 |
|- 4 | STATISTICS COLLECTOR | | 1 | | | 10 |00:00:00.01 | 4 |
| 5 | TABLE ACCESS FULL | T2 | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 4 |
| * 6 | INDEX UNIQUE SCAN | PK_T1 | 10 | 1 | 0 (0)| 10 |00:00:00.01 | 9 |
| * 7 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 1 | 1 (0)| 10 |00:00:00.01 | 10 |
|- 8 | TABLE ACCESS FULL | T1 | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$5DA710D3" "T2"@"SEL$2")
INDEX(@"SEL$5DA710D3" "T1"@"SEL$1" ("T1"."ID"))
LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "T1"@"SEL$1")
NLJ_BATCHING(@"SEL$5DA710D3" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"="ID" AND "NAME"="NAME")
6 - access("ID"="ID")
7 - filter("NAME"="NAME")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
52 rows selected.
8.換成exists看看.
select id,name from t2 where exists ( select null from t1 where t1.id=t2.id and t1.name =t2.name);
select id,name from t1 where exists ( select null from t2 where t2.id=t1.id and t2.name =t1.name);
SCOTT@test01p> @dpc '' outline,adaptive
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID by9m9cvtgybq8, child number 0
-------------------------------------
select id,name from t1 where exists ( select null from t2 where t2.id=t1.id and t2.name =t1.name)
Plan hash value: 1238133714
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 (100)| 10 |00:00:00.01 | 22 | | | |
|- * 1 | HASH JOIN | | 1 | 10 | 8 (0)| 10 |00:00:00.01 | 22 | 1096K| 1096K| |
| 2 | NESTED LOOPS | | 1 | | | 10 |00:00:00.01 | 22 | | | |
| 3 | NESTED LOOPS | | 1 | 10 | 8 (0)| 10 |00:00:00.01 | 12 | | | |
|- 4 | STATISTICS COLLECTOR | | 1 | | | 10 |00:00:00.01 | 3 | | | |
| 5 | SORT UNIQUE | | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 6 | TABLE ACCESS FULL | T2 | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 3 | | | |
| * 7 | INDEX UNIQUE SCAN | PK_T1 | 10 | 1 | 0 (0)| 10 |00:00:00.01 | 9 | | | |
| * 8 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 1 | 1 (0)| 10 |00:00:00.01 | 10 | | | |
|- 9 | TABLE ACCESS FULL | T1 | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$5DA710D3" "T2"@"SEL$2")
INDEX(@"SEL$5DA710D3" "T1"@"SEL$1" ("T1"."ID"))
LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "T1"@"SEL$1")
NLJ_BATCHING(@"SEL$5DA710D3" "T1"@"SEL$1")
SEMI_TO_INNER(@"SEL$5DA710D3" "T2"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."ID"="T1"."ID" AND "T2"."NAME"="T1"."NAME")
7 - access("T2"."ID"="T1"."ID")
8 - filter("T2"."NAME"="T1"."NAME")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
9. in ,exists後一點點小問題會漏掉id相等,name為NULL的情況,繼續測試:
insert into t2 values (11,null);
update t1 set name=null where id=11;
commt;
SCOTT@test01p> select /*+ OPT_PARAM('_convert_set_to_join' 'true') */ * from t1 intersect select * from t2;
ID NAME
---------- --------------------
7 testtest
8 testtest
2 testtest
5 testtest
1 testtest
3 testtest
6 testtest
10 testtest
4 testtest
9 testtest
11
11 rows selected.
--實際上看前面的執行計劃,就很容易理解filter(SYS_OP_MAP_NONNULL("T1"."NAME")=SYS_OP_MAP_NONNULL("T2"."NAME"))的含義.
SCOTT@test01p> select id,name from t1 where exists ( select null from t2 where t2.id=t1.id and t2.name =t1.name);
ID NAME
---------- --------------------
1 testtest
2 testtest
3 testtest
4 testtest
5 testtest
6 testtest
7 testtest
8 testtest
9 testtest
10 testtest
10 rows selected.
SCOTT@test01p> select id,name from t1 where (id,name) in ( select id,name from t2);
ID NAME
---------- --------------------
1 testtest
2 testtest
3 testtest
4 testtest
5 testtest
6 testtest
7 testtest
8 testtest
9 testtest
10 testtest
10 rows selected.
--如果使用in,exists,寫成這樣.
select id,name from t1 where (id,name) in ( select id,name from t2)
union all
select id,name from t1 where (id) in ( select id from t2 where t2.name is null) and t1.name is null ;
select id,name from t2 where (id,name) in ( select id,name from t2)
union all
select id,name from t2 where (id) in ( select id from t1 where t1.name is null) and t2.name is null ;
select id,name from t1 where exists ( select null from t2 where t2.id=t1.id and (t2.name =t1.name or (t1.name is null and t2.name is null)));
select id,name from t2 where exists ( select null from t1 where t1.id=t2.id and (t1.name =t2.name or (t2.name is null and t1.name is null)));
12.猜測函式SYS_OP_MAP_NONNULL的意義:
SCOTT@test01p> select SYS_OP_MAP_NONNULL(id) c10 ,SYS_OP_MAP_NONNULL(name) ,t2.*,dump(id,16) c20,dump(name,16) c40 from t2;
C10 SYS_OP_MAP_NONNULL_NAM ID NAME C20 C40
---------- ---------------------- ---------- -------------------- -------------------- ----------------------------------------
C10200 746573747465737400 1 testtest Typ=2 Len=2: c1,2 Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10300 746573747465737400 2 testtest Typ=2 Len=2: c1,3 Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10400 746573747465737400 3 testtest Typ=2 Len=2: c1,4 Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10500 746573747465737400 4 testtest Typ=2 Len=2: c1,5 Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10600 746573747465737400 5 testtest Typ=2 Len=2: c1,6 Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10700 746573747465737400 6 testtest Typ=2 Len=2: c1,7 Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10800 746573747465737400 7 testtest Typ=2 Len=2: c1,8 Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10900 746573747465737400 8 testtest Typ=2 Len=2: c1,9 Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10A00 746573747465737400 9 testtest Typ=2 Len=2: c1,a Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10B00 746573747465737400 10 testtest Typ=2 Len=2: c1,b Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10C00 FF 11 Typ=2 Len=2: c1,c NULL
11 rows selected.
--從輸出結果應該猜到大概.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1257033/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql INNER JOIN、LEFT JOIN、RIGHT JOIN；內連線（等值連線）、左連線、右連線MySql
- [20140823]12c null與預設值.txtNull
- Oracle(+)連線與Join連線Oracle
- LEFT JOIN 和JOIN 多表連線
- 表的連線方式:NESTED LOOP、HASH JOIN、SORT MERGE JOIN(轉)OOP
- Convert型別轉換型別
- 連線查詢簡析 join 、 left join 、 right join
- 外連線(outer join)示例
- 外連線轉換為內連線的情況
- Hibernate連線查詢join
- Oracle 的 hash join連線方式Oracle
- Oracle表連線操作——Hash Join（雜湊連線）下Oracle
- Oracle表連線操作——Hash Join（雜湊連線）上Oracle
- Oracle 連線因式分解(Join Factorization)Oracle
- 聊聊CBO的連線排列（Join Permutation）
- Oracle 內外連線 join 總結Oracle
- MySql的join（連線）查詢 （三表 left join 寫法）MySql
- Oracle表連線操作——Merge Sort Join（合併排序連線）Oracle排序
- [20181006]12c使用toad連線問題.txt
- SQLserver中用convert函式轉換日期格式SQLServer函式
- LINQ系列：LINQ to SQL Join連線SQL
- 表連線 join和(+)、union和uion allUI
- sql 連線查詢例項（left join）三表連線查詢SQL
- golang，interface轉換型別 cannot convert t (typGolang型別
- 網路地址轉換NAT原理及應用-連線跟蹤--埠轉換*******************
- Oracle查詢轉換（四）連線謂詞推入Oracle
- 多表連線的三種方式詳解 hash join、merge join、 nested loopOOP
- SQL語句中不同的連線JOIN及SQL中join的各種用法SQL
- [20140823]在sqlplus使用copy注意.txtSQL
- 文件轉換成圖片軟體(convert document to Image)
- [資料庫][SQL]圖解各種連線join資料庫SQL圖解
- mysql常用連線查詢join，left,right,crossMySqlROS
- Apache Spark SQL的高階Join連線技術ApacheSparkSQL
- 排序合併連線(sort merge join)的原理排序
- 通過swap_join_inputs來控制多表連線hash join中的build tableUI
- JavaScript陣列與字串相互轉換 join、splitJavaScript陣列字串
- PL/SQL Developer連線到Oracle 12cSQLDeveloperOracle
- Oracle 12c nocdb轉換成cdbOracle