[20160210]闭包传递2.txt

[20160210]闭包传递2.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/

--当时并没有考虑许多细节,仅仅按照例子重复在12c下测试看看:

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 t3 AS SELECT ROWNUM c1 FROM DUAL CONNECT BY LEVEL <= 10;
CREATE TABLE t4 AS SELECT MOD (ROWNUM, 10) + 100 c1 FROM DUAL CONNECT BY LEVEL <= 100;
CREATE TABLE t5 AS SELECT MOD (ROWNUM, 10) c1, RPAD ('X', 30) filler FROM DUAL CONNECT BY LEVEL <= 10000;

CREATE INDEX t5_i1 ON t5 (c1);

-- 分析表.Method_Opt => 'FOR ALL COLUMNS SIZE 1 '

2.测试:
SCOTT@test01p> alter session set statistics_level = all;
Session altered.

SCOTT@test01p> SELECT * FROM t3, t4, t5 WHERE t3.c1 = t4.c1 AND t4.c1 = t5.c1;
no rows selected

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bd3j0xbhyq4yx, child number 0
-------------------------------------
SELECT * FROM t3, t4, t5 WHERE t3.c1 = t4.c1 AND t4.c1 = t5.c1
Plan hash value: 3251999038
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 |        |       |     7 (100)|          |      0 |00:00:00.01 |       6 |       |       |          |
|   1 |  NESTED LOOPS                |       |      1 |        |       |            |          |      0 |00:00:00.01 |       6 |       |       |          |
|   2 |   NESTED LOOPS               |       |      1 |      1 |    41 |     7   (0)| 00:00:01 |      0 |00:00:00.01 |       6 |       |       |          |
|*  3 |    HASH JOIN                 |       |      1 |      1 |     7 |     6   (0)| 00:00:01 |      0 |00:00:00.01 |       6 |  2440K|  2440K| 1496K (0)|
|   4 |     TABLE ACCESS FULL        | T3    |      1 |     10 |    30 |     3   (0)| 00:00:01 |     10 |00:00:00.01 |       3 |       |       |          |
|   5 |     TABLE ACCESS FULL        | T4    |      1 |    100 |   400 |     3   (0)| 00:00:01 |    100 |00:00:00.01 |       3 |       |       |          |
|*  6 |    INDEX RANGE SCAN          | T5_I1 |      0 |   1000 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|   7 |   TABLE ACCESS BY INDEX ROWID| T5    |      0 |      1 |    34 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   4 - SEL$1 / T3@SEL$1
   5 - SEL$1 / T4@SEL$1
   6 - SEL$1 / T5@SEL$1
   7 - SEL$1 / T5@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T3"."C1"="T4"."C1")
   6 - access("T4"."C1"="T5"."C1")
Note
-----
   - this is an adaptive plan


SCOTT@test01p> SELECT * FROM t3, t4, t5 WHERE t3.c1 = t4.c1 AND t3.c1 = t5.c1;
no rows selected

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  g5007pk488f76, child number 0
-------------------------------------
SELECT * FROM t3, t4, t5 WHERE t3.c1 = t5.c1 AND t4.c1 = t5.c1
Plan hash value: 1630033643
-------------------------------------------------------------------------------------------------------------------------------------------------
| 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 |        |       |    24 (100)|          |      0 |00:00:00.01 |      60 |       |       |          |
|*  1 |  HASH JOIN          |      |      1 |      1 |    41 |    24   (0)| 00:00:01 |      0 |00:00:00.01 |      60 |   987K|   987K|  201K (0)|
|*  2 |   HASH JOIN         |      |      1 |      1 |    38 |    21   (0)| 00:00:01 |      0 |00:00:00.01 |      60 |  1969K|  1969K| 1086K (0)|
|   3 |    TABLE ACCESS FULL| T4   |      1 |    100 |   400 |     3   (0)| 00:00:01 |    100 |00:00:00.01 |       3 |       |       |          |
|   4 |    TABLE ACCESS FULL| T5   |      1 |  10000 |   332K|    18   (0)| 00:00:01 |  10000 |00:00:00.01 |      57 |       |       |          |
|   5 |   TABLE ACCESS FULL | T3   |      0 |     10 |    30 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / T4@SEL$1
   4 - SEL$1 / T5@SEL$1
   5 - SEL$1 / T3@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T3"."C1"="T5"."C1")
   2 - access("T4"."C1"="T5"."C1")
Note
-----
   - this is an adaptive plan

--12C依旧,虽然出现执行计划采用adaptive plan,但是连接顺序发生无法出现t3,t4连接的情况.

3.继续测试,加入提示看看:

SELECT /*+ leading(t3 t4 t5)  use_hash(t4) use_nl(t5) */ * FROM t3, t4, t5 WHERE t3.c1 = t5.c1 AND t4.c1 = t5.c1;

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0b7tsj1ajpq0r, child number 0
-------------------------------------
SELECT /*+ leading(t3 t4 t5)  use_hash(t4) use_nl(t5) */ * FROM t3, t4,
t5 WHERE t3.c1 = t5.c1 AND t4.c1 = t5.c1
Plan hash value: 3284867853
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 |        |       |  1017 (100)|          |      0 |00:00:00.01 |      14 |       |       |          |
|   1 |  NESTED LOOPS                |       |      1 |        |       |            |          |      0 |00:00:00.01 |      14 |       |       |          |
|   2 |   NESTED LOOPS               |       |      1 |      1 |    41 |  1017   (1)| 00:00:01 |      0 |00:00:00.01 |      14 |       |       |          |
|   3 |    MERGE JOIN CARTESIAN      |       |      1 |   1000 |  7000 |    15   (0)| 00:00:01 |   1000 |00:00:00.01 |       6 |       |       |          |
|   4 |     TABLE ACCESS FULL        | T3    |      1 |     10 |    30 |     3   (0)| 00:00:01 |     10 |00:00:00.01 |       3 |       |       |          |
|   5 |     BUFFER SORT              |       |     10 |    100 |   400 |    12   (0)| 00:00:01 |   1000 |00:00:00.01 |       3 |  4096 |  4096 | 4096  (0)|
|   6 |      TABLE ACCESS FULL       | T4    |      1 |    100 |   400 |     1   (0)| 00:00:01 |    100 |00:00:00.01 |       3 |       |       |          |
|*  7 |    INDEX RANGE SCAN          | T5_I1 |   1000 |    100 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       8 |       |       |          |
|   8 |   TABLE ACCESS BY INDEX ROWID| T5    |      0 |      1 |    34 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   4 - SEL$1 / T3@SEL$1
   6 - SEL$1 / T4@SEL$1
   7 - SEL$1 / T5@SEL$1
   8 - SEL$1 / T5@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - access("T4"."C1"="T5"."C1")
       filter("T3"."C1"="T5"."C1")

--刚使用提示时,可以发现问题依旧,T3,T4两个表的链接走的是不是hash join,而是CARTESIAN.
--加入t3.c1=t4.c1条件。

SCOTT@test01p> SELECT /*+ leading(t3 t4 t5)  use_hash(t4) use_nl(t5) */ * FROM t3, t4, t5 WHERE t3.c1 = t5.c1 AND t4.c1 = t5.c1 and t3.c1=t4.c1;
no rows selected

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9nzh9uxm6b11z, child number 0
-------------------------------------
SELECT /*+ leading(t3 t4 t5)  use_hash(t4) use_nl(t5) */ * FROM t3, t4,
t5 WHERE t3.c1 = t5.c1 AND t4.c1 = t5.c1 and t3.c1=t4.c1
Plan hash value: 3251999038
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 |        |       |     7 (100)|          |      0 |00:00:00.01 |       6 |       |       |          |
|   1 |  NESTED LOOPS                |       |      1 |        |       |            |          |      0 |00:00:00.01 |       6 |       |       |          |
|   2 |   NESTED LOOPS               |       |      1 |      1 |    41 |     7   (0)| 00:00:01 |      0 |00:00:00.01 |       6 |       |       |          |
|*  3 |    HASH JOIN                 |       |      1 |      1 |     7 |     6   (0)| 00:00:01 |      0 |00:00:00.01 |       6 |  2440K|  2440K| 1496K (0)|
|   4 |     TABLE ACCESS FULL        | T3    |      1 |     10 |    30 |     3   (0)| 00:00:01 |     10 |00:00:00.01 |       3 |       |       |          |
|   5 |     TABLE ACCESS FULL        | T4    |      1 |    100 |   400 |     3   (0)| 00:00:01 |    100 |00:00:00.01 |       3 |       |       |          |
|*  6 |    INDEX RANGE SCAN          | T5_I1 |      0 |    100 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|   7 |   TABLE ACCESS BY INDEX ROWID| T5    |      0 |      1 |    34 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   4 - SEL$1 / T3@SEL$1
   5 - SEL$1 / T4@SEL$1
   6 - SEL$1 / T5@SEL$1
   7 - SEL$1 / T5@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T3"."C1"="T4"."C1")
   6 - access("T4"."C1"="T5"."C1")
       filter("T3"."C1"="T5"."C1")

--作者的例子非常特殊.T3,T4连接返回记录为0.不过闭包传递确实存在一些问题.

4.自己做一些例子,测试看看:

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+1 id,rownum||'t3' name from dual connect by level<=5;

--分析表略.

select * from t1,t2,t3 where t1.id=t2.id and t2.id=t3.id;

Plan hash value: 261998084
-------------------------------------------------------------------------------------------------------------------------------------------------
| 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 |        |       |     9 (100)|          |      3 |00:00:00.01 |      10 |       |       |          |
|*  1 |  HASH JOIN          |      |      1 |      5 |   105 |     9   (0)| 00:00:01 |      3 |00:00:00.01 |      10 |  1451K|  1451K|  919K (0)|
|*  2 |   HASH JOIN         |      |      1 |      5 |    70 |     6   (0)| 00:00:01 |      4 |00:00:00.01 |       6 |  1888K|  1888K| 1091K (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |      5 |    35 |     3   (0)| 00:00:01 |      5 |00:00:00.01 |       3 |       |       |          |
|   4 |    TABLE ACCESS FULL| T2   |      1 |      5 |    35 |     3   (0)| 00:00:01 |      5 |00:00:00.01 |       3 |       |       |          |
|   5 |   TABLE ACCESS FULL | T3   |      1 |      5 |    35 |     3   (0)| 00:00:01 |      5 |00:00:00.01 |       4 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------

select * from t1,t2,t3 where t1.id=t3.id and t2.id=t3.id;

Plan hash value: 2308542799
-------------------------------------------------------------------------------------------------------------------------------------------------
| 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 |        |       |     9 (100)|          |      3 |00:00:00.01 |      10 |       |       |          |
|*  1 |  HASH JOIN          |      |      1 |      5 |   105 |     9   (0)| 00:00:01 |      3 |00:00:00.01 |      10 |  1451K|  1451K|  804K (0)|
|*  2 |   HASH JOIN         |      |      1 |      5 |    70 |     6   (0)| 00:00:01 |      3 |00:00:00.01 |       6 |  1888K|  1888K| 1090K (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |      5 |    35 |     3   (0)| 00:00:01 |      5 |00:00:00.01 |       3 |       |       |          |
|   4 |    TABLE ACCESS FULL| T3   |      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 |       4 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------

select /*+ leading (t1 t2 t3) */ * from t1,t2,t3 where t1.id=t3.id and t2.id=t3.id;
---------------------------------------------------------------------------------------------------------------------------------------------------
| 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 |        |       |    13 (100)|          |      3 |00:00:00.01 |      10 |       |       |          |
|*  1 |  HASH JOIN            |      |      1 |      5 |   105 |    13   (0)| 00:00:01 |      3 |00:00:00.01 |      10 |  1451K|  1451K| 1514K (0)|
|   2 |   MERGE JOIN CARTESIAN|      |      1 |     25 |   350 |    10   (0)| 00:00:01 |     25 |00:00:00.01 |       6 |       |       |          |
|   3 |    TABLE ACCESS FULL  | T1   |      1 |      5 |    35 |     3   (0)| 00:00:01 |      5 |00:00:00.01 |       3 |       |       |          |
|   4 |    BUFFER SORT        |      |      5 |      5 |    35 |     7   (0)| 00:00:01 |     25 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   5 |     TABLE ACCESS FULL | T2   |      1 |      5 |    35 |     1   (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 |       4 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------
--可以发现T1,T2的连接走的是CARTESIAN.看来闭包传递确实存在其它问题.