[20160213]闭包传递5.txt

[20160213]闭包传递5.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/
http://blog.itpub.net/267265/viewspace-1988061/


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 '
-- 正常的连接还包含其它字段的查询,测试看看. t1.id=t2.id  and t2.id=t3.id and t3.id=t4.id;

2.补充索引:

create unique index i_t1_id on t1(id);
create unique index i_t2_id on t2(id);
create unique index i_t3_id on t3(id);
create unique index i_t4_id on t4(id);
create index i_t1_name on t1(name);

3.继续测试:

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

Plan hash value: 848093300
-----------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |           |        |       |     4 (100)|          |
|   1 |  NESTED LOOPS                          |           |        |       |            |          |
|   2 |   NESTED LOOPS                         |           |      1 |    21 |     4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                        |           |      1 |    14 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1        |      1 |     7 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN                  | I_T1_NAME |      1 |       |     1   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID        | T2        |      1 |     7 |     1   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN                 | I_T2_ID   |      1 |       |     0   (0)|          |
|*  8 |    INDEX UNIQUE SCAN                   | I_T3_ID   |      1 |       |     0   (0)|          |
|   9 |   TABLE ACCESS BY INDEX ROWID          | T3        |      1 |     7 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   4 - SEL$1 / T1@SEL$1
   5 - SEL$1 / T1@SEL$1
   6 - SEL$1 / T2@SEL$1
   7 - SEL$1 / T2@SEL$1
   8 - SEL$1 / T3@SEL$1
   9 - SEL$1 / T3@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T1"."NAME"='4t1')
   7 - access("T1"."ID"="T2"."ID")
   8 - access("T3"."ID"="T2"."ID")
Note
-----
   - this is an adaptive plan
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

--可以看到连接顺序 t1,t2,t3.
--如果人为实现t1,t3,t2看看.

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

Plan hash value: 2643695722
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |        |       |     8 (100)|          |       |       |          |
|*  1 |  HASH JOIN                    |           |      1 |    21 |     8   (0)| 00:00:01 |  1451K|  1451K|  932K (0)|
|   2 |   MERGE JOIN CARTESIAN        |           |      5 |    70 |     5   (0)| 00:00:01 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1        |      1 |     7 |     2   (0)| 00:00:01 |       |       |          |
|*  4 |     INDEX RANGE SCAN          | I_T1_NAME |      1 |       |     1   (0)| 00:00:01 |       |       |          |
|   5 |    BUFFER SORT                |           |      5 |    35 |     3   (0)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|   6 |     TABLE ACCESS FULL         | T3        |      5 |    35 |     3   (0)| 00:00:01 |       |       |          |
|   7 |   TABLE ACCESS FULL           | T2        |      5 |    35 |     3   (0)| 00:00:01 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / T1@SEL$1
   4 - SEL$1 / T1@SEL$1
   6 - SEL$1 / T3@SEL$1
   7 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID" AND "T2"."ID"="T3"."ID")
   4 - access("T1"."NAME"='4t1')
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

--可以看出没有显示的写出条件 t1.id = t3.id ,出现的是CARTESIAN,而且t3,t2没有选择id索引.继续人为指定看看:

select /*+leading(t1 t3 t2) index(t3 I_T3_ID) index(t2 I_T2_ID) */ * from t1,t2,t3 where t1.id=t2.id  and t2.id=t3.id  and t1.name='4t1';

Plan hash value: 1569866173

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |           |        |       |     6 (100)|          |       |       |          |
|*  1 |  HASH JOIN                             |           |      1 |    21 |     6   (0)| 00:00:01 |  1451K|  1451K|  932K (0)|
|   2 |   MERGE JOIN CARTESIAN                 |           |      5 |    70 |     4   (0)| 00:00:01 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID         | T1        |      1 |     7 |     2   (0)| 00:00:01 |       |       |          |
|*  4 |     INDEX RANGE SCAN                   | I_T1_NAME |      1 |       |     1   (0)| 00:00:01 |       |       |          |
|   5 |    BUFFER SORT                         |           |      5 |    35 |     2   (0)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| T3        |      5 |    35 |     2   (0)| 00:00:01 |       |       |          |
|   7 |      INDEX FULL SCAN                   | I_T3_ID   |      5 |       |     1   (0)| 00:00:01 |       |       |          |
|   8 |   TABLE ACCESS BY INDEX ROWID BATCHED  | T2        |      5 |    35 |     2   (0)| 00:00:01 |       |       |          |
|   9 |    INDEX FULL SCAN                     | I_T2_ID   |      5 |       |     1   (0)| 00:00:01 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / T1@SEL$1
   4 - SEL$1 / T1@SEL$1
   6 - SEL$1 / T3@SEL$1
   7 - SEL$1 / T3@SEL$1
   8 - SEL$1 / T2@SEL$1
   9 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID" AND "T2"."ID"="T3"."ID")
   4 - access("T1"."NAME"='4t1')
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
--依旧出现MERGE JOIN CARTESIAN.

4.看来这个问题以前自己没有注意:
--补上t1.id=t3.id条件.
select /*+leading(t1 t3 t2) index(t3 I_T3_ID) index(t2 I_T2_ID) */ * from t1,t2,t3 where t1.id=t2.id  and t2.id=t3.id  and t1.name='4t1' and t1.id=t3.id;
Plan hash value: 1675215245
-----------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |           |        |       |     4 (100)|          |
|   1 |  NESTED LOOPS                          |           |        |       |            |          |
|   2 |   NESTED LOOPS                         |           |      1 |    21 |     4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                        |           |      1 |    14 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1        |      1 |     7 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN                  | I_T1_NAME |      1 |       |     1   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID        | T3        |      1 |     7 |     1   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN                 | I_T3_ID   |      1 |       |     0   (0)|          |
|*  8 |    INDEX UNIQUE SCAN                   | I_T2_ID   |      1 |       |     0   (0)|          |
|   9 |   TABLE ACCESS BY INDEX ROWID          | T2        |      1 |     7 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   4 - SEL$1 / T1@SEL$1
   5 - SEL$1 / T1@SEL$1
   6 - SEL$1 / T3@SEL$1
   7 - SEL$1 / T3@SEL$1
   8 - SEL$1 / T2@SEL$1
   9 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T1"."NAME"='4t1')
   7 - access("T1"."ID"="T3"."ID")
   8 - access("T2"."ID"="T3"."ID")
       filter("T1"."ID"="T2"."ID")
Note
-----
   - this is an adaptive plan
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

--这样就很好的使用索引.没有出现笛卡尔集.