ORACLE的直方图的一些试验

claimuser

SQL> col table_name format a20
SQL> col column_name format a20
SQL> col endpoint_number format a20
SQL> col endpoint_value format a20
SQL> set numwidth 5;
SQL> SELECT table_name,column_name,endpoint_number,endpoint_value FROM U
HISTOGRAMS WHERE TABLE_NAME='TAB';

TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- -------------------- --------------- --------------
TAB                  B                         ##########     ##########
TAB                  B                         ##########     ##########

为啥sqlplus是###，请教各位

Yong Huang

> 为啥sqlplus是###

set numw 12

12 should be big enough.

Yong Huang

shuangoracle

wei-xh 发表于 2010-5-8 14:08
在看看等高直方图的情况。
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',

hi，
我测试你例子时trace了下，如下：
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TAB  Alias: TAB
    #Rows: 10000  #Blks:  20  AvgRowLen:  6.00
Index Stats::
  Index: IX_TAB_B  Col#: 2
    LVLS: 1  #LB: 20  #DK: 10  LB/K: 2.00  DB/K: 1.00  CLUF: 19.00
***************************************
SINGLE TABLE ACCESS PATH
  Column (#2): B(NUMBER)
    AvgLen: 4.00 NDV: 10 Nulls: 0 Density: 0.9982 Min: 1 Max: 10000
    Histogram: HtBal  #Bkts: 8  UncompBkts: 8  EndPtVals: 3
  Table: TAB  Alias: TAB     
    Card: Original: 10000  Rounded: 9982  Computed: 9982.01  Non Adjusted: 9982.01
  Access Path: TableScan
    Cost:  6.21  Resp: 6.21  Degree: 0
      Cost_io: 6.00  Cost_cpu: 2342429
      Resp_io: 6.00  Resp_cpu: 2342429
  Access Path: index (AllEqRange)
    Index: IX_TAB_B
    resc_io: 39.00  resc_cpu: 3972296
    ix_sel: 0.9982  ix_sel_with_filters: 0.9982
    Cost: 39.35  Resp: 39.35  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 6.21  Degree: 1  Resp: 6.21  Card: 9982.01  Bytes: 0

tom一篇文章这样写的。
The Selectivity for popular values is calculated as the number of end points
spanned by that value divided by the total number of end points. Formula:

    Selectivity =     Number of end points spanned by this value
                  ------------------------------------------
                   total number of end points

那么如果按这个观点，
select * from tab where b=5;
占据了7个桶，也就是说selectivity=7/8=0.875
user_tab_columns里density确实是0.9982，trace出来ix_sel是0.9982，计算出来的card是9982，不知道是否研究过怎么算的？

magscott

那一般收集统计的时候，method_opt =>'for all columns size auto'，使用这个方法，是不是一个列的唯一值超过254个值，就是等高直方图，不超过254，就是等频直方图呢...

Yong Huang

> 那一般收集统计的时候，method_opt =>'for all columns size auto'，使用这个方法，是不是一个列的唯一值超过254个值，就是等高直方图，不超过254，就是等频直方图呢...

The type of a histogram is determined by not just the number of distinct values, but also the number of buckets. But I don't know the algorithm of that "size auto" in terms of how many buckets it creates.

I checked the definition of xxx_tab_col_statistics in 10g to see how it determines which of the two types of histogram. See part 2 of
http://yong321.freeshell.org/oranotes/Histogram.html

Although documentation says "Frequency histograms are automatically created instead of height-balanced histograms when the number of distinct values is less than or equal to the number of histogram buckets specified", I see no way to create a histogram with >= 255 buckets. So, if you have >= 255 distinct values, your bucket count is definitely smaller and therefore the histogram is definitely height-balanced. But if you have < 255 distinct values, either type is possible.

winsonjie

nice thank you