--------------创建测试表
create table generator as
select
rownum id
from all_objects
where rownum <= 2000
;
create table t1 (
skew not null,
padding
)
as
select
/*+ ordered use_nl(v2) */
v1.id,
rpad('x',400)
from
generator v1,
generator v2
where
v1.id <= 80
and v2.id <= 80
and v2.id <= v1.id
order by
v2.id,v1.id;
;
create index t1_i1 on t1(skew);
select count(*) from t1;
COUNT(*)
----------
3240
-----------------产生等高直方图
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 75'
);
end;
/
1)10G环境下
SQL> select
2 num_distinct, density, num_buckets
3 from
4 user_tab_columns
5 where
6 table_name = 'T1'
7 and column_name = 'SKEW'
8 ;
NUM_DISTINCT DENSITY NUM_BUCKETS
------------ ---------- -----------
80 .016563786 75
SQL> explain plan for
2 select * from t1 where skew=2;
已解释。
SQL> select * from table(dbms_xplan.display);
------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------
| 0 | SELECT STATEMENT | | 54 |
|* 1 | TABLE ACCESS FULL| T1 | 54 |
------------------------------------------
由于查询值2是个普通值,没有横跨任何桶,因此它的基数计算
=总行数*density=3240*0.016563786=54
可是11G后,不是这个算法了,ORACLE从新算了一个新的density
11G下
explain plan for
select * from t1 where skew=2;
select * from table(dbms_xplan.display);
------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------
| 0 | SELECT STATEMENT | | 29 |
|* 1 | TABLE ACCESS FULL| T1 | 29 |
------------------------------------------
apollo@CRMG>select
2 num_distinct, density, num_buckets
3 from
4 user_tab_columns
5 where
6 table_name = 'T1'
7 and column_name = 'SKEW'
8 ;
NUM_DISTINCT DENSITY NUM_BUCKETS
------------ ---------- -----------
80 .013973812 75
按照原来的算法=3240*0.013973812=45的。
从10053跟踪后可以看到ORACLE做了调整,不知道依据什么做出的调整。
哎。
---------------------10053内容
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]
Column (#1):
NewDensity:elect
num_distinct, density, num_buckets
from
user_tab_columns
where
table_name = 'T1'
and column_name = 'SKEW'
;
:q
.008958, OldDensity:0.013974 BktCnt:75, PopBktCnt:32, PopValCnt:16, NDV:80
Using density: 0.008958 of col #1 as selectivity of unpopular value pred
create table generator as
select
rownum id
from all_objects
where rownum <= 2000
;
create table t1 (
skew not null,
padding
)
as
select
/*+ ordered use_nl(v2) */
v1.id,
rpad('x',400)
from
generator v1,
generator v2
where
v1.id <= 80
and v2.id <= 80
and v2.id <= v1.id
order by
v2.id,v1.id;
;
create index t1_i1 on t1(skew);
select count(*) from t1;
COUNT(*)
----------
3240
-----------------产生等高直方图
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 75'
);
end;
/
1)10G环境下
SQL> select
2 num_distinct, density, num_buckets
3 from
4 user_tab_columns
5 where
6 table_name = 'T1'
7 and column_name = 'SKEW'
8 ;
NUM_DISTINCT DENSITY NUM_BUCKETS
------------ ---------- -----------
80 .016563786 75
SQL> explain plan for
2 select * from t1 where skew=2;
已解释。
SQL> select * from table(dbms_xplan.display);
------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------
| 0 | SELECT STATEMENT | | 54 |
|* 1 | TABLE ACCESS FULL| T1 | 54 |
------------------------------------------
由于查询值2是个普通值,没有横跨任何桶,因此它的基数计算
=总行数*density=3240*0.016563786=54
可是11G后,不是这个算法了,ORACLE从新算了一个新的density
11G下
explain plan for
select * from t1 where skew=2;
select * from table(dbms_xplan.display);
------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------
| 0 | SELECT STATEMENT | | 29 |
|* 1 | TABLE ACCESS FULL| T1 | 29 |
------------------------------------------
apollo@CRMG>select
2 num_distinct, density, num_buckets
3 from
4 user_tab_columns
5 where
6 table_name = 'T1'
7 and column_name = 'SKEW'
8 ;
NUM_DISTINCT DENSITY NUM_BUCKETS
------------ ---------- -----------
80 .013973812 75
按照原来的算法=3240*0.013973812=45的。
从10053跟踪后可以看到ORACLE做了调整,不知道依据什么做出的调整。
哎。
---------------------10053内容
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]
Column (#1):
NewDensity:elect
num_distinct, density, num_buckets
from
user_tab_columns
where
table_name = 'T1'
and column_name = 'SKEW'
;
:q
.008958, OldDensity:0.013974 BktCnt:75, PopBktCnt:32, PopValCnt:16, NDV:80
Using density: 0.008958 of col #1 as selectivity of unpopular value pred
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-697852/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22034023/viewspace-697852/