[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

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

| 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

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