等頻率直方圖計算基數

wei-xh發表於2011-06-13
直方圖
--------------建立測試表
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部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-697823/，如需轉載，請註明出處，否則將追究法律責任。

相關文章