传递闭包(transitive closure)和约束生成的谓词(Constraint-Generated Predicates)

传递闭包的英文为transitive closure,Wiki上有关于其的介绍。在Oracle优化器中也存在这一现象。
Jonathan lewis在Cost Based Oracle Fundermental一书中的第六章对这传递闭包进行了详细的介绍和试验,并给出一些版本下Oracle优化器是如何处理和存在的问题。Jonathan也在博客中写了关于这一话题的文章,分别在2007年的transitive-closure和2014年的closure

Transitive closure的意思是比如a=b,b=5,那么a=5。在optimizer中我们会看到执行计划的Predicate Information部分有谓词传递的现象。
本文的实验脚本来自Cost Based Oracle Fundermental一书第六章Selectivity Issues随书的脚本“trans_close_02.sql”和“constraint_03.sql”。
实验的环境为9.2.0.7(AIX),10.2.0.3(Solaris),11.2.0.4(Linux)和12.1.0.2(Linux)。

trans_close_02.sql
测试的sql语句:
select count(*) from t1, t2
where t1.n1 = 5 and t2.n1 = t1.n1;

9.2.0.7
Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=303 Card=1 Bytes=6)           
   1    0   SORT (AGGREGATE)                                                    
   2    1     MERGE JOIN (CARTESIAN) (Cost=303 Card=10000 Bytes=60000)   
   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=3 Card=100 Bytes=300)                                                      
   4    2       BUFFER (SORT) (Cost=300 Card=100 Bytes=300)               
   5    4         TABLE ACCESS (FULL) OF 'T2' (Cost=3 Card=100 Bytes=300)

10.2.0.3,11.2.0.4,12.1.0.2输出的cardinality相同,都为10000,cost在10g中为8,11g和12c中为10。
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  | this column for 11g,12c
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     6 |     8 | 10
|   1 |  SORT AGGREGATE     |      |     1 |     6 |       | 
|*  2 |   HASH JOIN         |      | 10000 | 60000 |     8 | 10
|*  3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     3 | 4
|*  4 |    TABLE ACCESS FULL| T2   |   100 |   300 |     3 | 4
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T2"."N1"="T1"."N1")
   3 - filter("T1"."N1"=5)
   4 - filter("T2"."N1"=5)

测试的sql语句:
select count(*) from t1, t2
where t1.n1 = 5 and t2.n1 = t1.n1 and t2.n1 = t1.n1;

9.2.0.7
Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=6)             
   1    0   SORT (AGGREGATE)                                                    
   2    1     HASH JOIN (Cost=7 Card=1000 Bytes=6000)                           
   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=3 Card=100 Bytes=300)                       
   4    2       TABLE ACCESS (FULL) OF 'T2' (Cost=3 Card=100 Bytes=300)    

  
10.2.0.3,11.2.0.4,12.1.0.2输出的cardinality相同,都为10000,cost在10g中为8,11g和12c中为10。
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  | this column for 11g,12c cost
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     6 |     8 | 10
|   1 |  SORT AGGREGATE     |      |     1 |     6 |       |
|*  2 |   HASH JOIN         |      | 10000 | 60000 |     8 | 10
|*  3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     3 | 4
|*  4 |    TABLE ACCESS FULL| T2   |   100 |   300 |     3 | 4
------------------------------------------------------------

测试的sql语句:
select count(*) from t1, t2
where t1.n1 = 5 and t2.n1 = t1.n1 and t2.n1 = 5;

9.2.0.7
Execution Plan
----------------------------------------------------------              
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=6)     
   1    0   SORT (AGGREGATE)                                           
   2    1     HASH JOIN (Cost=7 Card=1000 Bytes=6000)                  
   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=3 Card=100 Bytes=300) 
   4    2       TABLE ACCESS (FULL) OF 'T2' (Cost=3 Card=100 Bytes=300)  

10.2.0.3
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     6 |     8 |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |       |
|*  2 |   HASH JOIN         |      |  1000 |  6000 |     8 |
|*  3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     3 |
|*  4 |    TABLE ACCESS FULL| T2   |   100 |   300 |     3 |
------------------------------------------------------------

11.2.0.4 & 12.1.0.2
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     6 |    10 |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |       |
|*  2 |   HASH JOIN         |      | 10000 | 60000 |    10 |
|*  3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     4 |
|*  4 |    TABLE ACCESS FULL| T2   |   100 |   300 |     4 |
------------------------------------------------------------

测试的sql语句:
select count(*) from t1, t2
where t1.n1 = 5 and t2.n1 = t1.n1 + 0;
从下面的输出可以看到针对这种写法,优化器的输出没有变化。

9.2.0.7
Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=6)             
   1    0   SORT (AGGREGATE)                                                    
   2    1     HASH JOIN (Cost=7 Card=10000 Bytes=60000)                         
   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=3 Card=100 Bytes=300 )   
   4    2       TABLE ACCESS (FULL) OF 'T2' (Cost=3 Card=1000 Bytes=3000)    

10.2.0.3
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     6 |     8 |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |       |
|*  2 |   HASH JOIN         |      | 10000 | 60000 |     8 |
|*  3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     3 |
|   4 |    TABLE ACCESS FULL| T2   |  1000 |  3000 |     3 |
------------------------------------------------------------

11.2.0.4 & 12.1.0.2相同
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     6 |    10 |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |       |
|*  2 |   HASH JOIN         |      | 10000 | 60000 |    10 |
|*  3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     4 |
|   4 |    TABLE ACCESS FULL| T2   |  1000 |  3000 |     4 |
------------------------------------------------------------

接下来对约束生成的执行计划进行试验。
查询语句为
–from cost based oracle fundermental chapter6 online scripts  constraint_03.sql
create table t1
as
select
trunc((rownum-1)/15) n1,
trunc((rownum-1)/15) n2,
rpad(rownum,215) v1
from all_objects
where rownum <= 3000 ; create index t_i1 on t1(n1); alter table t1 modify n1 not null; alter table t1 modify n2 not null; alter table t1 add constraint t1_ck check (n1 >= n2);
—gather table statistics–
select count(t1.v1) ct_v1
from t1
where t1.n2 >= 180;

11.2.0.4的输出

---------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |   222 |    12 |
|   1 |  SORT AGGREGATE              |      |     1 |   222 |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1   |   301 | 66822 |    12 |
|*  3 |    INDEX RANGE SCAN          | T_I1 |   301 |       |     2 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."N2">=180)
   3 - access("N1">=180)

12.1.0.2的输出

-----------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |     1 |   222 |    12 |
|   1 |  SORT AGGREGATE                      |      |     1 |   222 |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |   301 | 66822 |    12 |
|*  3 |    INDEX RANGE SCAN                  | T_I1 |   301 |       |     2 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."N2">=180)
   3 - access("N1">=180)

在12.1.0.2中进行10053跟踪观察优化器的演进过程。

******************************************
----- Current SQL Statement for this session (sql_id=01r1wwzt80bpv) -----
explain plan for
select
    count(t1.v1)    ct_v1
from    t1
where   t1.n2 >= 180

redicate Move-Around (PM)
**************************
PM:     PM bypassed: Outer query contains no views.
PM:     PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"T1"."N2">=180
try to generate transitive predicate from check constraints for query block SEL$1 (#0)

constraint: "T1"."N1">="T1"."N2"

finally: "T1"."N2">=180 AND "T1"."N1">=180

FPD:   transitive predicates are generated in query block SEL$1 (#0)
"T1"."N2">=180 AND "T1"."N1">=180

=====================================
SPD: BEGIN context at statement level
=====================================
Stmt: ******* UNPARSED QUERY IS *******
SELECT COUNT("T1"."V1") "CT_V1" FROM "BLUE"."T1" "T1" WHERE "T1"."N2">=180 AND "T1"."N1">=180
Objects referenced in the statement
  T1[T1] 92421, type = 1
Objects in the hash table
  Hash table Object 92421, type = 1, ownerid = 12516463605811637622:
    No Dynamic Sampling Directives for the object
Return code in qosdInitDirCtx: ENBLD
===================================
SPD: END context at statement level
===================================
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT("T1"."V1") "CT_V1" FROM "BLUE"."T1" "T1" WHERE "T1"."N2">=180 AND "T1"."N1">=180
kkoqbc: optimizing query block SEL$1 (#0)

在执行计划中出现TABLE ACCESS BY INDEX ROWID BATCHED,关于这个话题的研究可以阅读一下pythian artical about batched table access这篇文章。

此条目发表在CBO, Uncategorized分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s