--------------建立測試表
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

蛋疼的CBO等高直方圖演算法

相關文章