[20160212]闭包传递4.txt

[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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1988061/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1988061/