[20160212]闭包传递4.txt
--所谓闭包传递是指sql语句的谓词条件A=B and B=C 可以推出 A=C. oracle 的 优化器能够利用这个特性优化sql语句。
--前一阵子看电子电子书<Apress.Expert.Oracle.SQL.Optimization.Deployment.and.Statistics.1430259779.pdf>测试链接:
http://blog.itpub.net/267265/viewspace-1981803/
http://blog.itpub.net/267265/viewspace-1987668/
--晚上以前学习oracle优化时,我练习过通过hint控制4个表的连接顺序问题.自己重复测试看看,难道4个表就没有问题吗?
1.环境:
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
create table t1 as select rownum id,rownum||'t1' name from dual connect by level<=5;
create table t2 as select rownum+1 id,rownum||'t2' name from dual connect by level<=5;
create table t3 as select rownum+2 id,rownum||'t3' name from dual connect by level<=5;
create table t4 as select rownum+3 id,rownum||'t3' name from dual connect by level<=5;
-- 分析表.Method_Opt => 'FOR ALL COLUMNS SIZE 1 '
2.如果要实现(t1,t2)->t4->t3 这样的连接顺序,如果要这样写:
select /*+ leading (t1 t2 t4 t3) */ * from t1,t2,t3,t4 where t1.id=t2.id and t2.id=t3.id and t3.id=t4.id;
Plan hash value: 3021121231
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 16 (100)| | 2 |00:00:00.25 | 13 | 20 | | | |
|* 1 | HASH JOIN | | 1 | 5 | 140 | 16 (0)| 00:00:01 | 2 |00:00:00.25 | 13 | 20 | 1245K| 1245K| 1158K (0)|
| 2 | MERGE JOIN CARTESIAN| | 1 | 25 | 525 | 13 (0)| 00:00:01 | 20 |00:00:00.19 | 9 | 14 | | | |
|* 3 | HASH JOIN | | 1 | 5 | 70 | 6 (0)| 00:00:01 | 4 |00:00:00.13 | 6 | 8 | 1888K| 1888K| 1091K (0)|
| 4 | TABLE ACCESS FULL | T1 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.04 | 3 | 2 | | | |
| 5 | TABLE ACCESS FULL | T2 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.04 | 3 | 6 | | | |
| 6 | BUFFER SORT | | 4 | 5 | 35 | 10 (0)| 00:00:01 | 20 |00:00:00.05 | 3 | 6 | 2048 | 2048 | 2048 (0)|
| 7 | TABLE ACCESS FULL | T4 | 1 | 5 | 35 | 1 (0)| 00:00:01 | 5 |00:00:00.05 | 3 | 6 | | | |
| 8 | TABLE ACCESS FULL | T3 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.06 | 4 | 6 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / T1@SEL$1
5 - SEL$1 / T2@SEL$1
7 - SEL$1 / T4@SEL$1
8 - SEL$1 / T3@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."ID"="T3"."ID" AND "T3"."ID"="T4"."ID")
3 - access("T1"."ID"="T2"."ID")
--因为t1,t2 与T4之间没有显式的定义相等.所以要很好的控制执行计划,必须显式定义:
select /*+ leading (t1 t2 t4 t3) */ * from t1,t2,t3,t4 where t1.id=t2.id and t2.id=t4.id and t3.id=t4.id;
~~~~~~~~~~~
--注意下划线部分修改的条件:
Plan hash value: 424258640
--------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 12 (100)| | 2 |00:00:00.04 | 13 | | | |
|* 1 | HASH JOIN | | 1 | 5 | 140 | 12 (0)| 00:00:01 | 2 |00:00:00.04 | 13 | 1245K| 1245K| 404K (0)|
|* 2 | HASH JOIN | | 1 | 5 | 105 | 9 (0)| 00:00:01 | 2 |00:00:00.04 | 9 | 1451K| 1451K| 973K (0)|
|* 3 | HASH JOIN | | 1 | 5 | 70 | 6 (0)| 00:00:01 | 4 |00:00:00.04 | 6 | 1888K| 1888K| 1094K (0)|
| 4 | TABLE ACCESS FULL| T1 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | | | |
| 5 | TABLE ACCESS FULL| T2 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | | | |
| 6 | TABLE ACCESS FULL | T4 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | | | |
| 7 | TABLE ACCESS FULL | T3 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 4 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / T1@SEL$1
5 - SEL$1 / T2@SEL$1
6 - SEL$1 / T4@SEL$1
7 - SEL$1 / T3@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."ID"="T4"."ID")
2 - access("T2"."ID"="T4"."ID")
3 - access("T1"."ID"="T2"."ID")
--这样就不会出现CARTESIAN.