Oracle 优化统计数据之直方图(histograms)(三)

PL/SQL procedure successfully completed.

SQL> select column_name,endpoint_number,endpoint_value from user_tab_histograms where
2 table_name='T1' and column_name='SKEW';

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- --------------
SKEW 1 1
SKEW 2 2
SKEW 3 3
SKEW 4 4
SKEW 5 5
SKEW 6 6
SKEW 7 7
SKEW 8 8
SKEW 9 9
SKEW 10 10
SKEW 10000 10000

第一个语句对列skew创建了有11个桶的直方图,因为我们知道列skew有11个不同的值.第二个语句显示了存储在数据字典视图中的直方图数据.直方图中存储的信息依赖于直方图的桶数小于列不同值的个数或者相等会有不同的解释,也就是说直方图中存储的信息依赖于直方图的类型会有不同的解释.下面解释频率直方图所代表的信息.

Endpoint_value显示的是真实的列值,endpoint_number显示的是累积的行数或者是累积的频率.为了计算一个特定列值的频率需使用与它相关的endpoint_number值减去它之前的累积值.
例如,对于endpoint_value为5的值,它的endpoint_number为5,之前的endpoint_number为4,因上skew=5的记录只有5-4=1行.类似的对于endpoint_value为10000的值它的endpoint_number为10000它之前的endpoint_number为10,所以skew=10000的记录有10000=10=9990行.

使用下面的sql来解释说明存储在数据字典中的直方图信息:
SQL> select endpoint_value as column_value,
2 endpoint_number as cummulative_frequency,
3 endpoint_number - lag(endpoint_number,1,0) over (order by endpoint_number) as frequency
4 from user_tab_histograms
5 where table_name ='T1' and column_name='SKEW';

COLUMN_VALUE CUMMULATIVE_FREQUENCY FREQUENCY
------------ --------------------- ----------
1 1 1
2 2 1
3 3 1
4 4 1
5 5 1
6 6 1
7 7 1
8 8 1
9 9 1
10 10 1
10000 10000 9990

存储总的或累积频率来代替单个频率在范围扫描时是特别有用的对于象where skew< =10这样的谓词基数就现成的.
现在因为我们对更skew创建了直方图再来查看之前的查询有什么不同:
SQL> select column_name,num_distinct,density,histogram from user_tab_col_statistics where table_name='T1';

COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM
------------------------------ ------------ ---------- ---------------
ALL_DISTINCT 10000 .0001 NONE
SKEW 11 .00005 FREQUENCY

SQL> explain plan for select * from t1 where skew=10000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9990 | 69930 | 7 (15)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 9990 | 69930 | 7 (15)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("SKEW"=10000)

13 rows selected.

SQL> explain plan for select * from t1 where skew=1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 3994350891

-------