如果在关联列中添加一个额外的预测,10.1版本数据库transitive Closure在处理SQL语句条件过程中,会剔除掉多余的预测条件,例如:
where colA =colB
and colA=5
则在Transitive Closure处理过程中,将条件转化成:
where colA = 5
and colB = 5
而关联条件colA=colB被剔除。
以上的剔除在CBO计算cardinality的时候,可能造成计算的错误。10.2版本将不再把关联条件剔除,所以其 转发成:
where colA = colB
and colA=5
and colB=5
以下是具体的实验:
1、创建测试脚本
SQL>execute dbms_random.seed(0)
SQL>create table t1
as
select
trunc(dbms_random.value(0, 30 )) join1,
trunc(dbms_random.value(0, 50 )) join2,
lpad(rownum,10) v1,
rpad('x',100) padding
from
all_objects
where
rownum <= 10000;
SQL>create table t2
as
select
trunc(dbms_random.value(0, 40 )) join1,
trunc(dbms_random.value(0, 40 )) join2,
lpad(rownum,10) v1,
rpad('x',100) padding
from
all_objects
where
rownum <= 10000;
2、统计分析(略)
3、10.1.0.2版本进行测试
SQL>set autotrace traceonly explain
SQL>select t1.v1, t2.v1
from
t1, t2
where
t1.join1 = 20
and t2.join1 = t1.join1
and t2.join2 = t1.join2
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1667 | 56678 | 83 (4)| 00:00:02 |
|* 1 | HASH JOIN | | 1667 | 56678 | 83 (4)| 00:00:02 |
|* 2 | TABLE ACCESS FULL | T2 | 250 | 4250 | 41 (3)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T1 | 333 | 5661 | 41 (3)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."JOIN2"="T1"."JOIN2")
2 - filter("T2"."JOIN1"=20)
3 - filter("T1"."JOIN1"=20)
从预测信息中我们看出剔除了t2.join1 = t1.join1的预测,在cardinality的计算也因此发生了偏差, 具体计算如下:
Join Selectivity(ID=3)= ((t1.num_rows - t1.null)/t1.num_rows)/30=1/30
Join cardinality(ID=3)= 10000 * Join Selectivity(ID=3)
= 10000 * 1/30 =333
Join Selectivity(ID=2)= ((t2.num_rows - t2.null)/t1.num_rows)/40=1/40
Join cardinality(ID=2)= 10000 * Join Selectivity(ID=2)
= 10000 * 1/40 =250
Join Selectivity(ID=1)= ((t1.num_rows(join2)- t1.null(join2))/t1.num_rows(join2)) *
((t2.num_rows(join2)- t2.null(join2))/t2.num_rows(join2)) *
1/max(t1.distinct_value(join2), t2.distinct_value(join2)
= 1/max(40,50)=1/50
Join cardinality(ID=1)= Join cardinality(ID=3)* Join cardinality(ID=2)*
Join Selectivity(ID=1)
= 1000/30 * 1000/40 * 1/50
= 1667
在10.2.0.1 测试以上脚本执行计划如下:
Execution Plan
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 1768 | 81 (2)| 00:00:01 |
|* 1 | HASH JOIN | | 52 | 1768 | 81 (2)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | T2 | 250 | 4250 | 40 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T1 | 333 | 5661 | 40 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."JOIN1"="T1"."JOIN1" AND "T2"."JOIN2"="T1"."JOIN2")
2 - filter("T2"."JOIN1"=20)
3 - filter("T1"."JOIN1"=20)
我们发现ID=1出现了变化,并没有剔除 t2.JOIN1=t1.JOIN1,所以CBO计算公式发生了如下变化
Join cardinality(ID3)=10000/30
Join cardinality(ID2)=10000/40
Join Selectivity(ID3)=1/max(t1.distinct_value(join1)* t1.distinct_value(join2),
t2.distinct_value(join1)* t2.distinct_value(join2))
= 1/max(1500,1600)=1/1600
Join cardinality(ID3)= 10000/30 * 10000/40 * 1/1600
= 10000/(12*16)
= 10000/192
= 52
所以在10.1版本以下,为了保证CBO能正确计算cardinality,语句需要修改为
SQL>select t1.v1, t2.v1
from
t1, t2
where
t1.join1 = 20
and t2.join1 = t1.join1
and t2.join2 = t1.join2
and t2.join1 = 20;
总之一句话,随着ORACLE版本升级,CBO计算越来越智能了。
where colA =colB
and colA=5
则在Transitive Closure处理过程中,将条件转化成:
where colA = 5
and colB = 5
而关联条件colA=colB被剔除。
以上的剔除在CBO计算cardinality的时候,可能造成计算的错误。10.2版本将不再把关联条件剔除,所以其 转发成:
where colA = colB
and colA=5
and colB=5
以下是具体的实验:
1、创建测试脚本
SQL>execute dbms_random.seed(0)
SQL>create table t1
as
select
trunc(dbms_random.value(0, 30 )) join1,
trunc(dbms_random.value(0, 50 )) join2,
lpad(rownum,10) v1,
rpad('x',100) padding
from
all_objects
where
rownum <= 10000;
SQL>create table t2
as
select
trunc(dbms_random.value(0, 40 )) join1,
trunc(dbms_random.value(0, 40 )) join2,
lpad(rownum,10) v1,
rpad('x',100) padding
from
all_objects
where
rownum <= 10000;
2、统计分析(略)
3、10.1.0.2版本进行测试
SQL>set autotrace traceonly explain
SQL>select t1.v1, t2.v1
from
t1, t2
where
t1.join1 = 20
and t2.join1 = t1.join1
and t2.join2 = t1.join2
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1667 | 56678 | 83 (4)| 00:00:02 |
|* 1 | HASH JOIN | | 1667 | 56678 | 83 (4)| 00:00:02 |
|* 2 | TABLE ACCESS FULL | T2 | 250 | 4250 | 41 (3)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T1 | 333 | 5661 | 41 (3)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."JOIN2"="T1"."JOIN2")
2 - filter("T2"."JOIN1"=20)
3 - filter("T1"."JOIN1"=20)
从预测信息中我们看出剔除了t2.join1 = t1.join1的预测,在cardinality的计算也因此发生了偏差, 具体计算如下:
Join Selectivity(ID=3)= ((t1.num_rows - t1.null)/t1.num_rows)/30=1/30
Join cardinality(ID=3)= 10000 * Join Selectivity(ID=3)
= 10000 * 1/30 =333
Join Selectivity(ID=2)= ((t2.num_rows - t2.null)/t1.num_rows)/40=1/40
Join cardinality(ID=2)= 10000 * Join Selectivity(ID=2)
= 10000 * 1/40 =250
Join Selectivity(ID=1)= ((t1.num_rows(join2)- t1.null(join2))/t1.num_rows(join2)) *
((t2.num_rows(join2)- t2.null(join2))/t2.num_rows(join2)) *
1/max(t1.distinct_value(join2), t2.distinct_value(join2)
= 1/max(40,50)=1/50
Join cardinality(ID=1)= Join cardinality(ID=3)* Join cardinality(ID=2)*
Join Selectivity(ID=1)
= 1000/30 * 1000/40 * 1/50
= 1667
在10.2.0.1 测试以上脚本执行计划如下:
Execution Plan
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 1768 | 81 (2)| 00:00:01 |
|* 1 | HASH JOIN | | 52 | 1768 | 81 (2)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | T2 | 250 | 4250 | 40 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T1 | 333 | 5661 | 40 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."JOIN1"="T1"."JOIN1" AND "T2"."JOIN2"="T1"."JOIN2")
2 - filter("T2"."JOIN1"=20)
3 - filter("T1"."JOIN1"=20)
我们发现ID=1出现了变化,并没有剔除 t2.JOIN1=t1.JOIN1,所以CBO计算公式发生了如下变化
Join cardinality(ID3)=10000/30
Join cardinality(ID2)=10000/40
Join Selectivity(ID3)=1/max(t1.distinct_value(join1)* t1.distinct_value(join2),
t2.distinct_value(join1)* t2.distinct_value(join2))
= 1/max(1500,1600)=1/1600
Join cardinality(ID3)= 10000/30 * 10000/40 * 1/1600
= 10000/(12*16)
= 10000/192
= 52
所以在10.1版本以下,为了保证CBO能正确计算cardinality,语句需要修改为
SQL>select t1.v1, t2.v1
from
t1, t2
where
t1.join1 = 20
and t2.join1 = t1.join1
and t2.join2 = t1.join2
and t2.join1 = 20;
总之一句话,随着ORACLE版本升级,CBO计算越来越智能了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/354732/viewspace-623136/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/354732/viewspace-623136/