优化器何时使用density作为选择率

ser_tab_columns视图里，有两列NUM_DISTINCT和DENSITY，如果没有收集直方图，这两列的值是相同的，

SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true,estimate_percent => null,method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> select table_name,column_name,num_distinct,density from user_tab_columns where table_name='T1' and column_name='OBJECT_NAME';

TABLE_NAME                     COLUMN_NAME                              NUM_DISTINCT    DENSITY
------------------------------ ---------------------------------------- ------------ ----------
T1                             OBJECT_NAME                                      8605 .000116212

SQL> select 1/8605 from dual;

    1/8605
----------
.000116212

而如果收集了直方图，这两列的值就是不同的了。

SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true,estimate_percent => null,method_opt=>'for all columns size 199');

PL/SQL procedure successfully completed.

SQL> select table_name,column_name,num_distinct,density from user_tab_columns where table_name='T1' and column_name='OBJECT_NAME';

TABLE_NAME                     COLUMN_NAME                              NUM_DISTINCT    DENSITY
------------------------------ ---------------------------------------- ------------ ----------
T1                             OBJECT_NAME                                      8605 .000136562

但优化器到底在什么情况下使用这个density，这是个问题，根据《基于成本的oracle优化法则》一书，通过下面的测试揭开了答案：
SQL> define m_demo_size=80
SQL>
SQL> drop table t1;

begin
        begin           execute immediate 'purge recyclebin';
        exception       when others then null;
        end;

        begin           execute immediate 'begin dbms_stats.delete_system_stats; end;';
        exception       when others then null;
        end;

        begin           execute immediate 'alter session set "_optimizer_cost_model"=io';
        exception       when others then null;
        end;

end;
/
Table dropped.

SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15 

PL/SQL procedure successfully completed.

SQL> drop table generator;
create table generator as
select
        rownum  id
from    all_objects
where   rownum <= 2000
;

Table dropped.

SQL>   2    3    4    5    6 
Table created.

SQL> SQL>
SQL> create table t1 (
  2          skew            not null,
  3          padding
  4  )
  5  as
  6  /*
  7  with generator as (
  8          select  --+ materialize
  9                  rownum  id
 10          from    all_objects
 11          where   rownum <= 5000
 12  )
 13  */
 14  select
 15          /*+ ordered use_nl(v2) */
 16          v1.id,
 17          rpad('x',400)
 18  from
 19          generator       v1,
 20          generator       v2
 21  where
 22          v1.id <= &m_demo_size
 23  and     v2.id <= &m_demo_size
 24  and     v2.id <= v1.id
 25  order by
 26          v2.id,v1.id
 27  ;
old  22:         v1.id <= &m_demo_size
new  22:         v1.id <= 80
old  23: and     v2.id <= &m_demo_size
new  23: and     v2.id <= 80

Table created.

SQL> create index t1_i1 on t1(skew);

Index created.

SQL> select
  2          count(*)
  3  from
  4          t1
  5  ;

  COUNT(*)
----------
      3240

SQL> select
  2          skew, count(*)
  3  from
  4          t1
  5  group by
  6          skew
  7  order by
  8          skew
  9  ;

      SKEW   COUNT(*)
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9
        10         10
        11         11
        12         12
        13         13
        14         14
        15         15
        16         16
        17         17
        18         18
        19         19
        20         20
        21         21
        22         22
        23         23
        24         24
        25         25
        26         26
        27         27
        28         28
        29         29
        30         30
        31         31
        32         32
        33         33
        34         34
        35         35
        36         36
        37         37
        38         38
        39         39
        40         40
        41         41
        42         42
        43         43
        44         44
        45         45
        46         46
        47         47
        48         48
        49         49
        50         50
        51         51
        52         52
        53         53
        54         54
        55         55
        56         56
        57         57
        58         58
        59         59
        60         60
        61         61
        62         62
        63         63
        64         64
        65         65
        66         66
        67         67
        68         68
        69         69
        70         70
        71         71
        72         72
        73         73
        74         74
        75         75
        76         76
        77         77
        78         78
        79         79
        80         80

80 rows selected.

SQL> begin
  2          dbms_stats.gather_table_stats(
  3                  user,
  4                  't1',
  5                  cascade => true,
  6                  estimate_percent => null,
  7                  method_opt => 'for all columns size 120'
  8          );
  9  end;
 10  /

PL/SQL procedure successfully completed.

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 .000154321          80

----通过如下查询，skew=100，显然超出了上限值80，查看oracle是如何确定其选择率：

SQL> alter system flush shared_pool;

System altered.

SQL> alter session set events '10053 trace name context forever,level 1';

Session altered.

SQL> select
  2          skew, padding
  3  from
  4          t1
  5  where
        skew = 100
;  6    7 

no rows selected

#################10053 trace####################################
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#1): SKEW(NUMBER)
    AvgLen: 3.00 NDV: 80 Nulls: 0 Density: 1.5432e-04 Min: 1 Max: 80
    Histogram: Freq  #Bkts: 80  UncompBkts: 3240  EndPtVals: 80
  Using prorated density: 1.5432e-04 of col #1 as selectivity of out-of-range value pred
  Table: T1  Alias: T1
    Card: Original: 3240  Rounded: 1  Computed: 0.50  Non Adjusted: 0.50
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  53.08  Resp: 53.08  Degree: 0
      Cost_io: 53.00  Cost_cpu: 2008215
      Resp_io: 53.00  Resp_cpu: 2008215
  Using prorated density: 1.5432e-04 of col #1 as selectivity of out-of-range value pred
  Access Path: index (AllEqRange)
    Index: T1_I1
    resc_io: 2.00  resc_cpu: 15463
    ix_sel: 1.5432e-04  ix_sel_with_filters: 1.5432e-04
    Cost: 2.00  Resp: 2.00  Degree: 1
  Best:: AccessPath: IndexRange  Index: T1_I1
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 0.50  Bytes: 0
***************************************
#################10053 trace####################################

其中Using prorated density: 1.5432e-04 of col #1 as selectivity of out-of-range value pred，很明显的表明了当谓词值超出了上下限范围后，优化器选择了
density作为估计的selectivity。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-667462/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-667462/