oracle直方图基数,等频率直方图计算基数

--------------创建测试表

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-------------------------------------------

| 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在等频直方图里有啥用