[20160211]闭包传递3.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.以前我记得我学习优化时,听过一些优化的课程,里面提到如何能很好的控制4个表的连接顺序,能很好地解决一些问题.
我当时觉得很简单,实际上就是使用提示leading ,SWAP_JOIN_INPUTS 就很好的控制连接顺序.举一个例子:
假设要使用t4->(t2,t3)->t1 这样的连接顺序,使用的提示是:
leading (t2 t3 t4 t1) SWAP_JOIN_INPUTS(t4)

--测试看看:
select /*+ leading (t2 t3 t4 t1) SWAP_JOIN_INPUTS(t4) */ * from t1,t2,t3,t4 where t1.id=t2.id and t2.id=t3.id and t3.id=t4.id;

Plan hash value: 1146301945
--------------------------------------------------------------------------------------------------------------------------------------------------
| 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.01 |      13 |       |       |          |
|* 1 | HASH JOIN           |      |      1 |      5 |   140 |    12   (0)| 00:00:01 |      2 |00:00:00.01 |      13 | 1245K| 1245K| 557K (0)|
|* 2 |   HASH JOIN          |      |      1 |      5 |   105 |     9   (0)| 00:00:01 |      3 |00:00:00.01 |       9 | 1888K| 1888K| 1093K (0)|
|   3 |    TABLE ACCESS FULL | T4   |      1 |      5 |    35 |     3   (0)| 00:00:01 |      5 |00:00:00.01 |       3 |       |       |          |
|* 4 |    HASH JOIN         |      |      1 |      5 |    70 |     6   (0)| 00:00:01 |      4 |00:00:00.01 |       6 | 1888K| 1888K| 1064K (0)|
|   5 |     TABLE ACCESS FULL| T2   |      1 |      5 |    35 |     3   (0)| 00:00:01 |      5 |00:00:00.01 |       3 |       |       |          |
|   6 |     TABLE ACCESS FULL| T3   |      1 |      5 |    35 |     3   (0)| 00:00:01 |      5 |00:00:00.01 |       3 |       |       |          |
|   7 |   TABLE ACCESS FULL | T1   |      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
   3 - SEL$1 / T4@SEL$1
   5 - SEL$1 / T2@SEL$1
   6 - SEL$1 / T3@SEL$1
   7 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID")
   2 - access("T3"."ID"="T4"."ID")
   4 - access("T2"."ID"="T3"."ID")

3.当然我的问题要测试闭包问题,修改连接条件如下:
select /*+ leading (t2 t3 t4 t1) SWAP_JOIN_INPUTS(t4) */ * from t1,t2,t3,t4 where t1.id=t2.id and t2.id=t4.id and t3.id=t4.id;

--修改t2.id=t3.id为t2.id=t4.id,这样实际上暗含有t2.id=t3.id条件的.看看执行计划:

Plan hash value: 2268006538
----------------------------------------------------------------------------------------------------------------------------------------------------
| 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 |        |       |    16 (100)|          |      2 |00:00:00.01 |      13 |       |       |          |
|* 1 | HASH JOIN             |      |      1 |      5 |   140 |    16   (0)| 00:00:01 |      2 |00:00:00.01 |      13 | 1245K| 1245K| 525K (0)|
|* 2 |   HASH JOIN            |      |      1 |      5 |   105 |    13   (0)| 00:00:01 |      3 |00:00:00.01 |       9 | 1645K| 1645K| 1211K (0)|
|   3 |    TABLE ACCESS FULL   | T4   |      1 |      5 |    35 |     3   (0)| 00:00:01 |      5 |00:00:00.01 |       3 |       |       |          |
|   4 |    MERGE JOIN CARTESIAN|      |      1 |     25 |   350 |    10   (0)| 00:00:01 |     25 |00:00:00.01 |       6 |       |       |          |
|   5 |     TABLE ACCESS FULL | T2   |      1 |      5 |    35 |     3   (0)| 00:00:01 |      5 |00:00:00.01 |       3 |       |       |          |
|   6 |     BUFFER SORT        |      |      5 |      5 |    35 |     7   (0)| 00:00:01 |     25 |00:00:00.01 |       3 | 2048 | 2048 | 2048 (0)|
|   7 |      TABLE ACCESS FULL | T3   |      1 |      5 |    35 |     1   (0)| 00:00:01 |      5 |00:00:00.01 |       3 |       |       |          |
|   8 |   TABLE ACCESS FULL    | T1   |      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
   3 - SEL$1 / T4@SEL$1
   5 - SEL$1 / T2@SEL$1
   7 - SEL$1 / T3@SEL$1
   8 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID")
   2 - access("T2"."ID"="T4"."ID" AND "T3"."ID"="T4"."ID")

--注意看T2,T3的连接使用了CARTESIAN,看来是自己以前忽略了这个问题.

4.按照作者观点,测试加入一个常量看看:

select /*+ leading (t2 t3 t4 t1) SWAP_JOIN_INPUTS(t4) */ * from t1,t2,t3,t4 where t1.id=t2.id and t2.id=t4.id and t3.id=t4.id and t1.id=4;

Plan hash value: 2268006538
----------------------------------------------------------------------------------------------------------------------------------------------------
| 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)|          |      1 |00:00:00.02 |      13 |       |       |          |
|* 1 | HASH JOIN             |      |      1 |      1 |    28 |    12   (0)| 00:00:01 |      1 |00:00:00.02 |      13 | 1245K| 1245K| 697K (0)|
|* 2 |   HASH JOIN            |      |      1 |      1 |    21 |     9   (0)| 00:00:01 |      1 |00:00:00.01 |       9 | 1645K| 1645K| 731K (0)|
|* 3 |    TABLE ACCESS FULL   | T4   |      1 |      1 |     7 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|   4 |    MERGE JOIN CARTESIAN|      |      1 |      1 |    14 |     6   (0)| 00:00:01 |      1 |00:00:00.01 |       6 |       |       |          |
|* 5 |     TABLE ACCESS FULL | T2   |      1 |      1 |     7 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|   6 |     BUFFER SORT        |      |      1 |      1 |     7 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 | 2048 | 2048 | 2048 (0)|
|* 7 |      TABLE ACCESS FULL | T3   |      1 |      1 |     7 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|* 8 |   TABLE ACCESS FULL    | T1   |      1 |      1 |     7 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / T4@SEL$1
   5 - SEL$1 / T2@SEL$1
   7 - SEL$1 / T3@SEL$1
   8 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID")
   2 - access("T2"."ID"="T4"."ID" AND "T3"."ID"="T4"."ID")
   3 - filter("T4"."ID"=4)
   5 - filter("T2"."ID"=4)
   7 - filter("T3"."ID"=4)
   8 - filter("T1"."ID"=4)

-- 注意看里面的filter条件.看来以后优化时要注意这个问题.

时间： 2024-11-06 10:02:12

[20160211]闭包传递3.txt

[20160211]闭包传递3.txt的相关文章

[20160212]闭包传递4.txt

[20160213]闭包传递5.txt

[20160210]闭包传递2.txt

[20160125]闭包传递问题.txt

[20160318]push_pred hint使用疑惑.txt

Swift使用闭包表达式

《从零开始学Swift》学习笔记（Day 22）——闭包那些事儿！

IOS swift3.0 下闭包语法整理_IOS

《数据科学：R语言实现》——1.6　理解闭包