--------------创建测试表
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
-----------------看看效果如何
select
      skew, count(*)
from
      t1
group by
      skew
order by
      skew
;

   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

-----------------分析表
begin
dbms_stats.gather_table_stats(
  user,
  't1',
  cascade => true,
  estimate_percent => null,
  method_opt => 'for all columns size 80'-----------------产生等频直方图
);
end;
/

1）等值条件下的基数
select
skew, padding
from
t1
where
skew = 70
;
------------------------------------------
| Id  | Operation       | Name | Rows  |
------------------------------------------
| 0 | SELECT STATEMENT  |    | 70 |
|*  1 |  TABLE ACCESS FULL| T1 | 70 |
------------------------------------------

基数  =查看直方图可以获得此值出现的频率=70

2）select
skew, padding
from
t1
where
skew between 70 and 80;
-------------------------------------------
| Id  | Operation       | Name | Rows  |
-------------------------------------------
| 0 | SELECT STATEMENT  |    | 825 |
|*  1 |  TABLE ACCESS FULL| T1 | 825 |
-------------------------------------------
基数  =70+71+。。+80=825

3）绑定变量的情况下
select
skew, padding
from
t1
where
skew=:a;
------------------------------------------
| Id  | Operation       | Name | Rows  |
------------------------------------------
| 0 | SELECT STATEMENT  |    | 41 |
|*  1 |  TABLE ACCESS FULL| T1 | 41 |
------------------------------------------

基数  =选择率*总行数=1/num_distinct*3240=40.5~~41
注意，这里ORACLE取的并不是density的值，而只是1/num_distinct的值。

4）开区间的情况下
select
skew, padding
from
t1
where
skew>:a;
------------------------------------------
| Id  | Operation       | Name | Rows  |
------------------------------------------
| 0 | SELECT STATEMENT  |    | 162 |
|*  1 |  TABLE ACCESS FULL| T1 | 162 |
------------------------------------------
基数  =总行数*5%=3240*5%=162

5）闭区间的情况下
select
skew, padding
from
t1
where
skew>:a and skew<:b;
-------------------------------------------
| Id  | Operation       | Name | Rows  |
-------------------------------------------
| 0 | SELECT STATEMENT |    | 162 |
|*  1 |  FILTER          |    |    |
|*  2 | TABLE ACCESS FULL| T1 | 162 |
-------------------------------------------
这里我的情况还是跟上面开区间一样，你的版本如果不是11.1.0.7很可能结果是8
即基数  =总行数*5%*5%=3240*5%*5%=8

6）如果不在取值范围内
select
skew, padding
from
t1
where
skew=-1；
-----------------------------------------------------
| Id  | Operation                | Name  | Rows  |
-----------------------------------------------------
| 0 | SELECT STATEMENT          |    |    1 |
| 1 |  TABLE ACCESS BY INDEX ROWID| T1 |    1 |
|*  2 | INDEX RANGE SCAN       | T1_I1 |    1 |
-----------------------------------------------------

select
skew, padding
from
t1
where
skew between 100 and 120;
-----------------------------------------------------
| Id  | Operation                | Name  | Rows  |
-----------------------------------------------------
| 0 | SELECT STATEMENT          |    |    1 |
| 1 |  TABLE ACCESS BY INDEX ROWID| T1 |    1 |
|*  2 | INDEX RANGE SCAN       | T1_I1 |    1 |
-----------------------------------------------------
能够看出如果查询的值不在记录范围内，ORACLE估算出来的都是1.

还有等频直方图下的density=1/(2*num_rows),暂时还不知道这个density在等频直方图里有啥用

ITPub博客

等频率直方图计算基数

最新文章