微课sql优化(7)、统计信息收集(5)-关于直方图

1、直方图介绍

你可以为列统计直方图。这些直方图为列数据的分布情况提供准确的估算。当列数据倾斜时，直方图提供更为优化的选择性估算,为数据分布不均匀的情况提供最优的执行计划。

Oracle Database为提供2种类别的列统计信息直方图：

Height-Balanced Histograms
Frequency Histograms

数据库存储直方图信息*TAB_COL_STATISTICS视图(用户和DBA)。列值范围：HEIGHTBALANCED, FREQUENCY, or NONE。

2、Height-Balanced Histograms

在height-balanced histogram中，列值被划分为桶，使得每个桶包含大致相同数量的行。直方图显示端点在值范围内的位置。

考虑一个my_col值为1到100之间的列，以及一个10个桶的直方图。如果数据my_col均匀分布，则直方图看起来与图13-1类似，其中数字是端点值。例如，第七个桶具有值在60到70之间的行。

图13-1具有均匀分布的高度平衡直方图

每个桶中的行数为总行数的10％。在这个均匀分布的例子中，40％的行的值在60到100之间。

如果数据不均匀分布，则直方图可能如图13-2所示。在这种情况下，大多数行的列的值为5。只有10％的行的值在60到100之间。

图13-2具有非均匀分布的高度平衡直方图

您可以使用USER_TAB_HISTOGRAMS表格查看高度平衡的直方图，如示例13-1所示。

BEGIN
DBMS_STATS . GATHER_table_STATS (
OWNNAME = > 'OE' ,
TABNAME = > 'INVENTORIES' ,
METHOD_OPT = > 'FOR COLUMNS SIZE 10 quantity_on_hand' ) ;
END ;
/
SELECT COLUMN_NAME , NUM_DISTINCT , NUM_BUCKETS , HISTOGRAM
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'QUANTITY_ON_HAND' ;
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
QUANTITY_ON_HAND 237 10 HEIGHT BALANCED
SELECT ENDPOINT_NUMBER , ENDPOINT_VALUE
FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'QUANTITY_ON_HAND'
ORDER BY ENDPOINT_NUMBER ;
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 0
1 27
2 42
3 57
4 74
5 98
6 123
7 149
8 175
9 202
10 353

在示例13-1查询输出中，一行（1-10）对应于直方图中的每个桶。Oracle数据库向该直方图添加了特殊的第0个数据桶，因为第1个数据桶（27）中的值不是quantity_on_hand列的最小值。第0个桶的最小值为0 quantity_on_hand。

3、 frequency histogra m

在 frequency histogram中，列的每个值对应于直方图的单个桶。每个桶包含此单个值的出现次数。例如，假设36行包含列的值1 warehouse_id。端点值1具有端点号36。

数据库在以下条件下自动创建频率直方图，而不是高度平衡的直方图：

不同值的数量小于或等于指定的直方图桶数（最多254个）。
每个列值重复一次。

您可以使用USER_TAB_HISTOGRAMS视图查看频率直方图，如示例13-2所示。

BEGIN
DBMS_STATS . GATHER_TABLE_STATS (
OWNNAME = > 'OE' ,
TABNAME = > 'INVENTORIES' ,
METHOD_OPT = > 'FOR COLUMNS SIZE 20 warehouse_id' ) ;
END ;
/
SELECT COLUMN_NAME , NUM_DISTINCT , NUM_BUCKETS , HISTOGRAM
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'WAREHOUSE_ID' ;
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
WAREHOUSE_ID 9 9 FREQUENCY
SELECT ENDPOINT_NUMBER , ENDPOINT_VALUE
FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'WAREHOUSE_ID'
ORDER BY ENDPOINT_NUMBER ;
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
36 1
213 2
261 3
370 4
484 5
692 6
798 7
984 8
1112 9

在例13-2中，第一个桶为warehouse_id1。该值在表中显示36次，如以下查询所证实：

oe@PROD> SELECT COUNT(*) FROM inventories WHERE warehouse_id = 1;

COUNT(*)

----------

5、练习4 、直方图优化练习

统计已销户用户数量，请优化以下语句

select count(1) from ht.c_cons where status='close';

SQL > select status , count ( 1 ) from ht . c_cons group by status ;
STATUS COUNT ( 1 )
------------------------------------------------------------ ----------
close 19
open 9519
creating 462
SQL > create index ht . idx_c_cons_status on ht . c_cons ( status ) ;
SQL > col owner for a10
col table_name for a20
col column_name for a20
col data_type for a30
col histogram for a20
select owner , table_name , column_name , data_type ,
column_id , num_distinct , histogram , NUM_NULLS , LAST_ANALYZED from
dba_tab_columns where table_name = 'C_CONS' and owner = 'HT'
order by column_id ; SQL > SQL > SQL > SQL > SQL > 2 3 4
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE COLUMN_ID NUM_DISTINCT HISTOGRAM NUM_NULLS LAST_ANALYZED
---------- -------------------- -------------------- ------------------------------ ---------- ------------ -------------------- ---------- ------------------------------
HT C_CONS CONS_NO NUMBER 1 10000 NONE 0 20 - AUG - 17
HT C_CONS CONS_NAME VARCHAR2 2 5057 NONE 0 20 - AUG - 17
HT C_CONS ORG_NAME VARCHAR2 3 12 NONE 0 20 - AUG - 17
HT C_CONS BUILD_DATE DATE 4 10000 NONE 0 20 - AUG - 17
HT C_CONS STATUS VARCHAR2 5 3 NONE 0 20 - AUG - 17
SQL > exec DBMS_STATS . GATHER_TABLE_STATS ( ownname = > 'HT' , tabname = > 'C_CONS' , estimate_percent = > 30 , method_opt = > 'for columns size 50 status' , no_invalidate = > FALSE , degree = > 4 , cascade = > TRUE ) ;
PL / SQL procedure successfully completed .
SQL > col owner for a10
SQL > col table_name for a20
col column_name for a20
col data_type for a30
col histogram for a20
select owner , table_name , column_name , data_type ,
column_id , num_distinct , histogram , NUM_NULLS , LAST_ANALYZED from
dba_tab_columns where table_name = 'C_CONS' and owner = 'HT'
order by column_id ; SQL > SQL > SQL > SQL > 2 3 4
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE COLUMN_ID NUM_DISTINCT HISTOGRAM NUM_NULLS LAST_ANALYZED
---------- -------------------- -------------------- ------------------------------ ---------- ------------ -------------------- ---------- ------------------------------
HT C_CONS CONS_NO NUMBER 1 10000 NONE 0 20 - AUG - 17
HT C_CONS CONS_NAME VARCHAR2 2 5057 NONE 0 20 - AUG - 17
HT C_CONS ORG_NAME VARCHAR2 3 12 NONE 0 20 - AUG - 17
HT C_CONS BUILD_DATE DATE 4 10000 NONE 0 20 - AUG - 17
HT C_CONS STATUS VARCHAR2 5 3 FREQUENCY 0 20 - AUG - 17
SQL > select count ( 1 ) from ht . c_cons where status = 'open' ;
Execution Plan
----------------------------------------------------------
Plan hash value : 2016425671
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ( % CPU ) | Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 8 ( 0 ) | 00 : 00 : 01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| * 2 | INDEX FAST FULL SCAN | IDX_C_CONS_STATUS | 9639 | 57834 | 8 ( 0 ) | 00 : 00 : 01 |
-------------------------------------------------------------------------------------------
Predicate Information ( identified by operation id ) :
---------------------------------------------------
2 - filter ( "STATUS" = 'open' )
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL * Net to client
523 bytes received via SQL * Net from client
2 SQL * Net roundtrips to / from client
0 sorts ( memory )
0 sorts ( disk )
1 rows processed
SQL >
SQL > select count ( 1 ) from ht . c_cons where status = 'close' ;
Execution Plan
----------------------------------------------------------
Plan hash value : 2292286995
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ( % CPU ) | Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1 ( 0 ) | 00 : 00 : 01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| * 2 | INDEX RANGE SCAN | IDX_C_CONS_STATUS | 24 | 144 | 1 ( 0 ) | 00 : 00 : 01 |
---------------------------------------------------------------------------------------
Predicate Information ( identified by operation id ) :
---------------------------------------------------
2 - access ( "STATUS" = 'close' )
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL * Net to client
523 bytes received via SQL * Net from client
2 SQL * Net roundtrips to / from client
0 sorts ( memory )
0 sorts ( disk )
1 rows processed

当前文章：微课sql优化(7)、统计信息收集(5)-关于直方图
地址分享：http://scyanting.com/article/ipdppo.html

微课sql优化(7)、统计信息收集(5)-关于直方图

1、直方图介绍

3、 frequency histogra m

5、 练习4 、直方图优化练习

其他资讯

5、练习4 、直方图优化练习