【DB笔试面试637】在Oracle中，直方图使用示例。
【DB笔试面试637】在Oracle中，直方图使用示例。
小麦苗DBA宝典
发布于 2019-09-29 16:24:33
发布于 2019-09-29 16:24:33
题目部分
在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程序员面试笔试宝典》，作者：小麦苗
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2019-09-10，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读