【DB笔试面试637】在Oracle中，直方图使用示例。

• 2019 年 10 月 10 日
• 笔记

题目部分

在Oracle中，直方图使用示例。

♣

答案部分

下面给出直方图的一个示例，先准备数据分布不均衡的一张表：

CREATE TABLE T_ST_20170604_LHR AS SELECT ROWNUM ID,ROWNUM SAL FROM DUAL CONNECT BY LEVEL<=10000;  UPDATE T_ST_20170604_LHR SET SAL=5000 WHERE SAL BETWEEN 6 AND 9995;  --9990  CREATE INDEX T_ST_20170604_LHR_SAL ON T_ST_20170604_LHR(SAL);  EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_ST_20170604_LHR',CASCADE=>TRUE,METHOD_OPT=>'FOR COLUMNS SAL SIZE 1');  

查询数据信息：

SYS@orclasm > SET LINESIZE 9999  SYS@orclasm > COL COLUMN_NAME FORMAT A15  SYS@orclasm > SELECT TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE FROM DBA_TAB_HISTOGRAMS WHERE TABLE_NAME='T_ST_20170604_LHR';  TABLE_NAME                     COLUMN_NAME     ENDPOINT_NUMBER ENDPOINT_VALUE  ------------------------------ --------------- --------------- --------------  T_ST_20170604_LHR              SAL                           0              1  T_ST_20170604_LHR              SAL                           1          10000    SYS@orclasm > SELECT D.COLUMN_NAME,    2         D.NUM_DISTINCT,    3         D.NUM_NULLS,    4         D.NUM_BUCKETS,    5         D.HISTOGRAM    6    FROM DBA_TAB_COL_STATISTICS D    7   WHERE D.TABLE_NAME = 'T_ST_20170604_LHR';    COLUMN_NAME     NUM_DISTINCT  NUM_NULLS NUM_BUCKETS HISTOGRAM  --------------- ------------ ---------- ----------- ---------------  SAL                       11          0           1 NONE  

列SAL上只有最大值，最小值两条记录分别对应端点号（ENDPOINT_NUMBER）0和1，这种显示说明列SAL没有直方图信息，从DBA_TAB_COL_STATISTICS的HISTOGRAM列值为NONE也说明列SAL没有直方图。

SYS@orclasm > SET AUTOT TRACE  SYS@orclasm > SELECT * FROM T_ST_20170604_LHR WHERE SAL=1;      Execution Plan  ----------------------------------------------------------  Plan hash value: 738598333    -----------------------------------------------------------------------------------------------------  | Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |  -----------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |                       |   909 |  6363 |     4   (0)| 00:00:01 |  |   1 |  TABLE ACCESS BY INDEX ROWID| T_ST_20170604_LHR     |   909 |  6363 |     4   (0)| 00:00:01 |  |*  2 |   INDEX RANGE SCAN          | T_ST_20170604_LHR_SAL |   909 |       |     2   (0)| 00:00:01 |  -----------------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("SAL"=1)      Statistics  ----------------------------------------------------------            0  recursive calls            0  db block gets            4  consistent gets            0  physical reads            0  redo size          592  bytes sent via SQL*Net to client          520  bytes received via SQL*Net from client            2  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)            1  rows processed    SYS@orclasm > SELECT * FROM T_ST_20170604_LHR WHERE SAL=5000;    9990 rows selected.    Execution Plan  ----------------------------------------------------------  Plan hash value: 738598333    -----------------------------------------------------------------------------------------------------  | Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |  -----------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |                       |   909 |  6363 |     4   (0)| 00:00:01 |  |   1 |  TABLE ACCESS BY INDEX ROWID| T_ST_20170604_LHR     |   909 |  6363 |     4   (0)| 00:00:01 |  |*  2 |   INDEX RANGE SCAN          | T_ST_20170604_LHR_SAL |   909 |       |     2   (0)| 00:00:01 |  -----------------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("SAL"=5000)    Statistics  ----------------------------------------------------------            0  recursive calls            0  db block gets         1368  consistent gets            0  physical reads            0  redo size       244008  bytes sent via SQL*Net to client         7835  bytes received via SQL*Net from client          667  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)         9990  rows processed    SYS@orclasm > SELECT ROUND(10000*(1/11)) FROM DUAL;    ROUND(10000*(1/11))  -------------------                  909  

SAL列为等值查询，全部选择索引范围扫描，且预估行数（Rows）均为909。Rows的值来源于：ROUND(NUM_ROWS*(1/NUM_DISTINCT_VAL))=ROUND(10000*(1/11))=909，和执行计划里的909相吻合，因为没有收集列的直方图信息，所以优化器估算返回行数和实际返回行数还是有不少差距。

下面针对SAL列收集直方图：

SYS@orclasm > SET AUTOT OFF  SYS@orclasm > EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_ST_20170604_LHR',CASCADE=>TRUE,METHOD_OPT=>'FOR COLUMNS SAL SIZE AUTO');    PL/SQL procedure successfully completed.    SYS@orclasm > SELECT D.COLUMN_NAME,D.NUM_DISTINCT,D.NUM_NULLS,D.NUM_BUCKETS,D.HISTOGRAM FROM DBA_TAB_COL_STATISTICS D WHERE D.TABLE_NAME = 'T_ST_20170604_LHR';    COLUMN_NAME     NUM_DISTINCT  NUM_NULLS NUM_BUCKETS HISTOGRAM  --------------- ------------ ---------- ----------- ---------------  SAL                       11          0          11 FREQUENCY      SYS@orclasm > SELECT TABLE_NAME,COLUMN_NAME,ENDPOINT_VALUE,ENDPOINT_NUMBER,NVL((ENDPOINT_NUMBER-(LAG(ENDPOINT_NUMBER) OVER (ORDER BY ENDPOINT_VALUE))),ENDPOINT_NUMBER) COUNTS FROM DBA_TAB_HISTOGRAMS WHERE TABLE_NAME='T_ST_20170604_LHR';    TABLE_NAME                     COLUMN_NAME     ENDPOINT_VALUE ENDPOINT_NUMBER     COUNTS  ------------------------------ --------------- -------------- --------------- ----------  T_ST_20170604_LHR              SAL                          1               1          1  T_ST_20170604_LHR              SAL                          2               2          1  T_ST_20170604_LHR              SAL                          3               3          1  T_ST_20170604_LHR              SAL                          4               4          1  T_ST_20170604_LHR              SAL                          5               5          1  T_ST_20170604_LHR              SAL                       5000            9995       9990  T_ST_20170604_LHR              SAL                       9996            9996          1  T_ST_20170604_LHR              SAL                       9997            9997          1  T_ST_20170604_LHR              SAL                       9998            9998          1  T_ST_20170604_LHR              SAL                       9999            9999          1  T_ST_20170604_LHR              SAL                      10000           10000          1    11 rows selected.    SYS@orclasm > SELECT SAL ENDPOINT_VALUE,SUM(COUNT(*)) OVER(ORDER BY SAL RANGE UNBOUNDED PRECEDING) ENDPOINT_NUMBER,COUNT(*) COUNTS FROM T_ST_20170604_LHR T GROUP BY T.SAL;    ENDPOINT_VALUE ENDPOINT_NUMBER     COUNTS  -------------- --------------- ----------               1               1          1               2               2          1               3               3          1               4               4          1               5               5          1            5000            9995       9990            9996            9996          1            9997            9997          1            9998            9998          1            9999            9999          1           10000           10000          1  11 rows selected.    SYS@orclasm >  

由于列SAL唯一值的个数没有超过254，所以Oracle自动收集频率直方图。NUM_BUCKETS表示桶数，一共有11个Buckets，所以在DBA_TAB_HISTOGRAMS中，ENDPOINT_VALUE列记录的就是这11个不同的DISTINCT值。ENDPOINT_NUMBER则记录了到此DISTINCT值为止累加的行数。

SYS@orclasm > SET AUTOT TRACE  SYS@orclasm > SELECT * FROM T_ST_20170604_LHR WHERE SAL=1;      Execution Plan  ----------------------------------------------------------  Plan hash value: 738598333    -----------------------------------------------------------------------------------------------------  | Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |  -----------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |                       |     1 |     7 |     2   (0)| 00:00:01 |  |   1 |  TABLE ACCESS BY INDEX ROWID| T_ST_20170604_LHR     |     1 |     7 |     2   (0)| 00:00:01 |  |*  2 |   INDEX RANGE SCAN          | T_ST_20170604_LHR_SAL |     1 |       |     1   (0)| 00:00:01 |  -----------------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("SAL"=1)      Statistics  ----------------------------------------------------------            0  recursive calls            0  db block gets            4  consistent gets            0  physical reads            0  redo size          592  bytes sent via SQL*Net to client          520  bytes received via SQL*Net from client            2  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)            1  rows processed  SYS@orclasm > SELECT * FROM T_ST_20170604_LHR WHERE SAL=5000;    9990 rows selected.      Execution Plan  ----------------------------------------------------------  Plan hash value: 513072079    ---------------------------------------------------------------------------------------  | Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |  ---------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT  |                   |  9990 | 69930 |     7   (0)| 00:00:01 |  |*  1 |  TABLE ACCESS FULL| T_ST_20170604_LHR |  9990 | 69930 |     7   (0)| 00:00:01 |  ---------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       1 - filter("SAL"=5000)      Statistics  ----------------------------------------------------------            0  recursive calls            0  db block gets         1368  consistent gets            0  physical reads            0  redo size       244008  bytes sent via SQL*Net to client         7835  bytes received via SQL*Net from client          667  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)         9990  rows processed  

在SAL=1时候使用了索引扫描，而SAL=5000时候，已经使用全表扫描了，说明直方图起了作用。从执行计划的Rows部分也可以看出Oracle计算出来的Cardinality是9990，和实际的情况完全吻合（这里的Rows为CURRENT_ENDPOINT_NUMBER-PREVIOUS_ENDPOINT_NUMBER）。可以看出这种频率直方图统计的列的信息是非常精确的。

下面重新对列SAL收集直方图，让Bucket的数量为9，小于SAL列的DISTINCT的值，那么Oracle会收集高度直方图。

SYS@orclasm > SET AUTOT OFF  SYS@orclasm > EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_ST_20170604_LHR',CASCADE=>TRUE,METHOD_OPT=>'FOR COLUMNS SAL SIZE 9');    PL/SQL procedure successfully completed.    SYS@orclasm > SELECT D.COLUMN_NAME,D.NUM_DISTINCT,D.NUM_NULLS,D.NUM_BUCKETS,D.HISTOGRAM FROM DBA_TAB_COL_STATISTICS D WHERE D.TABLE_NAME = 'T_ST_20170604_LHR';    COLUMN_NAME     NUM_DISTINCT  NUM_NULLS NUM_BUCKETS HISTOGRAM  --------------- ------------ ---------- ----------- ---------------  SAL                       11          0           9 HEIGHT BALANCED    SYS@orclasm > SELECT TABLE_NAME,COLUMN_NAME,ENDPOINT_VALUE,ENDPOINT_NUMBER FROM DBA_TAB_HISTOGRAMS WHERE TABLE_NAME='T_ST_20170604_LHR';    TABLE_NAME                     COLUMN_NAME     ENDPOINT_VALUE ENDPOINT_NUMBER  ------------------------------ --------------- -------------- ---------------  T_ST_20170604_LHR              SAL                          1               0  T_ST_20170604_LHR              SAL                       5000               8  T_ST_20170604_LHR              SAL                      10000               9  

在高度平衡直方图中，在DBA_TAB_HISTOGRAMS视图中，EDNPOINT_NUMBER代表桶号，且自动省去EDNPOINT_VALUE值相同且ENDPOINT_NUMBER相邻的桶的值（节省空间，合并存储）。ENDPOINT_VALUE表示每一个桶中的最大值，而第一个桶记录的是最小值（Bucket为0的行，即EDNPOINT_NUMBER为0的行）。重复出现为ENDPOINT_VALUE的值称为Popular Value，这里的0即Popular Value。显然，Popular Value所在记录的ENDPOINT_NUMBER值和它上一条记录的ENDPOINT_NUMBER值之间的差值越大，则意味着该Popular Value在目标表中所占的比例也就越大，它所对应的Cardinality也就越大。

一共有9个桶（不包含0号Bucket），在该直方图图中，1到7号桶被省略存储，说明和8号桶的存储是一样的。

SYS@orclasm > SET AUTOT TRACE  SYS@orclasm > SELECT * FROM T_ST_20170604_LHR WHERE SAL=1;      Execution Plan  ----------------------------------------------------------  Plan hash value: 738598333    -----------------------------------------------------------------------------------------------------  | Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |  -----------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |                       |   111 |   777 |     2   (0)| 00:00:01 |  |   1 |  TABLE ACCESS BY INDEX ROWID| T_ST_20170604_LHR     |   111 |   777 |     2   (0)| 00:00:01 |  |*  2 |   INDEX RANGE SCAN          | T_ST_20170604_LHR_SAL |   111 |       |     1   (0)| 00:00:01 |  -----------------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("SAL"=1)      Statistics  ----------------------------------------------------------            0  recursive calls            0  db block gets            4  consistent gets            0  physical reads            0  redo size          592  bytes sent via SQL*Net to client          520  bytes received via SQL*Net from client            2  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)            1  rows processed    SYS@orclasm > SELECT * FROM T_ST_20170604_LHR WHERE SAL=5000;    9990 rows selected.      Execution Plan  ----------------------------------------------------------  Plan hash value: 513072079    ---------------------------------------------------------------------------------------  | Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |  ---------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT  |                   |  8889 | 62223 |     7   (0)| 00:00:01 |  |*  1 |  TABLE ACCESS FULL| T_ST_20170604_LHR |  8889 | 62223 |     7   (0)| 00:00:01 |  ---------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       1 - filter("SAL"=5000)      Statistics  ----------------------------------------------------------            0  recursive calls            0  db block gets         1368  consistent gets            0  physical reads            0  redo size       244008  bytes sent via SQL*Net to client         7835  bytes received via SQL*Net from client          667  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)         9990  rows processed    SYS@orclasm >  

在高度平衡直方图中执行计划的列的选择性就不是那么的精确了，而在现实很多时候，列的唯一值是超过254的，那么只能使用高度平衡直方图了。

如果需要删除直方图信息，在Oracle 10g中可以通过设置“METHOD_OPT=>'FOR COLUMNS SAL SIZE 1'”，但这却得再次收集表的统计信息，十分不合理，所以，在Oracle 11g中，有如下方法可以直接删除直方图信息：

EXEC DBMS_STATS.DELETE_COLUMN_STATS(USER,'T_ST_20170604_LHR','SAL',COL_STAT_TYPE => 'HISTOGRAM');  

其中，COL_STAT_TYPE默认为ALL，表示删除列的基本统计信息和直方图信息。

本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗