[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 levelcreate table t2 as select rownum id ,cast('testtest' as varchar2(10)) name from dual connect by levelcreate 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.
--从输出结果应该猜到大概.