微课sql优化(7)、统计信息收集(5)-关于直方图
1、直方图介绍
你可以为列统计直方图。这些直方图为列数据的分布情况提供准确的估算。当列数据倾斜时，直方图提供更为优化的选择性估算,为数据分布不均匀的情况提供最优的执行计划。Oracle Database为提供2种类别的列统计信息直方图：Height-Balanced HistogramsFrequency 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所示。BEGINDBMS_STATS.GATHER_table_STATS(OWNNAME=>'OE',TABNAME=>'INVENTORIES',METHOD_OPT=>'FOR COLUMNS SIZE 10 quantity_on_hand');END;/
SELECTCOLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,HISTOGRAMFROMUSER_TAB_COL_STATISTICSWHERETABLE_NAME='INVENTORIES'ANDCOLUMN_NAME='QUANTITY_ON_HAND';
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM------------------------------ ------------ ----------- ---------------QUANTITY_ON_HAND 237 10 HEIGHT BALANCED
SELECTENDPOINT_NUMBER,ENDPOINT_VALUEFROMUSER_TAB_HISTOGRAMSWHERETABLE_NAME='INVENTORIES'ANDCOLUMN_NAME='QUANTITY_ON_HAND'ORDERBYENDPOINT_NUMBER;
ENDPOINT_NUMBER ENDPOINT_VALUE--------------- --------------0 01 272 423 574 745 986 1237 1498 1759 20210 353在示例13-1查询输出中，一行（1-10）对应于直方图中的每个桶。Oracle数据库向该直方图添加了特殊的第0个数据桶，因为第1个数据桶（27）中的值不是quantity_on_hand列的最小值。第0个桶的最小值为0 quantity_on_hand。3、frequency histogram
在frequency histogram中，列的每个值对应于直方图的单个桶。每个桶包含此单个值的出现次数。例如，假设36行包含列的值1 warehouse_id。端点值1具有端点号36。数据库在以下条件下自动创建频率直方图，而不是高度平衡的直方图：不同值的数量小于或等于指定的直方图桶数（最多254个）。每个列值重复一次。您可以使用USER_TAB_HISTOGRAMS视图查看频率直方图，如示例13-2所示。BEGINDBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'OE',TABNAME=>'INVENTORIES',METHOD_OPT=>'FOR COLUMNS SIZE 20 warehouse_id');END;/
SELECTCOLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,HISTOGRAMFROMUSER_TAB_COL_STATISTICSWHERETABLE_NAME='INVENTORIES'ANDCOLUMN_NAME='WAREHOUSE_ID';
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM------------------------------ ------------ ----------- ---------------WAREHOUSE_ID 9 9 FREQUENCY
SELECTENDPOINT_NUMBER,ENDPOINT_VALUEFROMUSER_TAB_HISTOGRAMSWHERETABLE_NAME='INVENTORIES'ANDCOLUMN_NAME='WAREHOUSE_ID'ORDERBYENDPOINT_NUMBER;
ENDPOINT_NUMBER ENDPOINT_VALUE--------------- --------------36 1213 2261 3370 4484 5692 6798 7984 81112 9在例13-2中，第一个桶为warehouse_id1。该值在表中显示36次，如以下查询所证实：oe@PROD> SELECT COUNT(*) FROM inventories WHERE warehouse_id = 1; COUNT(*)---------- 365、练习4、直方图优化练习
统计已销户用户数量，请优化以下语句select count(1) from ht.c_cons where status='close';SQL>selectstatus,count(1)fromht.c_consgroupbystatus;
STATUSCOUNT(1)------------------------------------------------------------ ----------close 19open 9519creating 462
SQL>createindexht.idx_c_cons_statusonht.c_cons(status);SQL>colownerfora10coltable_namefora20colcolumn_namefora20coldata_typefora30colhistogramfora20selectowner,table_name,column_name,data_type,column_id,num_distinct,histogram,NUM_NULLS,LAST_ANALYZEDfromdba_tab_columnswheretable_name='C_CONS'andowner='HT'orderbycolumn_id;SQL>SQL>SQL>SQL>SQL>2 3 4
OWNER TABLE_NAMECOLUMN_NAME DATA_TYPE COLUMN_ID NUM_DISTINCT HISTOGRAM NUM_NULLS LAST_ANALYZED---------- -------------------- -------------------- ------------------------------ ---------- ------------ -------------------- ---------- ------------------------------HT C_CONSCONS_NO NUMBER 1 10000 NONE 0 20-AUG-17HT C_CONSCONS_NAMEVARCHAR2 2 5057 NONE 0 20-AUG-17HT C_CONSORG_NAMEVARCHAR2 3 12 NONE 0 20-AUG-17HT C_CONSBUILD_DATEDATE 4 10000 NONE 0 20-AUG-17HT C_CONSSTATUSVARCHAR2 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>colownerfora10SQL>coltable_namefora20colcolumn_namefora20coldata_typefora30colhistogramfora20selectowner,table_name,column_name,data_type,column_id,num_distinct,histogram,NUM_NULLS,LAST_ANALYZEDfromdba_tab_columnswheretable_name='C_CONS'andowner='HT'orderbycolumn_id;SQL>SQL>SQL>SQL>2 3 4
OWNER TABLE_NAMECOLUMN_NAME DATA_TYPE COLUMN_ID NUM_DISTINCT HISTOGRAM NUM_NULLS LAST_ANALYZED---------- -------------------- -------------------- ------------------------------ ---------- ------------ -------------------- ---------- ------------------------------HT C_CONSCONS_NO NUMBER 1 10000 NONE 0 20-AUG-17HT C_CONSCONS_NAMEVARCHAR2 2 5057 NONE 0 20-AUG-17HT C_CONSORG_NAMEVARCHAR2 3 12 NONE 0 20-AUG-17HT C_CONSBUILD_DATEDATE 4 10000 NONE 0 20-AUG-17HT C_CONSSTATUSVARCHAR2 5 3 FREQUENCY 0 20-AUG-17SQL>selectcount(1)fromht.c_conswherestatus='open';Execution Plan----------------------------------------------------------Plan hashvalue:2016425671
-------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|6|8(0)|00:00:01||1|SORT AGGREGATE||1|6||||*2|INDEXFAST FULL SCAN|IDX_C_CONS_STATUS|9639|57834|8(0)|00:00:01|-------------------------------------------------------------------------------------------
Predicate Information(identifiedbyoperation id):---------------------------------------------------2-filter("STATUS"='open')Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 28 consistent gets 0 physical reads 0 redosize527 bytes sent via SQL*Nettoclient523 bytes received via SQL*Netfromclient 2 SQL*Net roundtripsto/fromclient 0 sorts(memory) 0 sorts(disk) 1rowsprocessedSQL>SQL>selectcount(1)fromht.c_conswherestatus='close';Execution Plan----------------------------------------------------------
Plan hashvalue:2292286995
---------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|6|1(0)|00:00:01||1|SORT AGGREGATE||1|6||||*2|INDEXRANGE SCAN|IDX_C_CONS_STATUS|24|144|1(0)|00:00:01|---------------------------------------------------------------------------------------
Predicate Information(identifiedbyoperation id):---------------------------------------------------
2-access("STATUS"='close')
Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redosize526 bytes sent via SQL*Nettoclient523 bytes received via SQL*Netfromclient 2 SQL*Net roundtripsto/fromclient 0 sorts(memory) 0 sorts(disk) 1rowsprocessed
你可以为列统计直方图。这些直方图为列数据的分布情况提供准确的估算。当列数据倾斜时，直方图提供更为优化的选择性估算,为数据分布不均匀的情况提供最优的执行计划。Oracle Database为提供2种类别的列统计信息直方图：Height-Balanced HistogramsFrequency 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所示。BEGINDBMS_STATS.GATHER_table_STATS(OWNNAME=>'OE',TABNAME=>'INVENTORIES',METHOD_OPT=>'FOR COLUMNS SIZE 10 quantity_on_hand');END;/
SELECTCOLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,HISTOGRAMFROMUSER_TAB_COL_STATISTICSWHERETABLE_NAME='INVENTORIES'ANDCOLUMN_NAME='QUANTITY_ON_HAND';
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM------------------------------ ------------ ----------- ---------------QUANTITY_ON_HAND 237 10 HEIGHT BALANCED
SELECTENDPOINT_NUMBER,ENDPOINT_VALUEFROMUSER_TAB_HISTOGRAMSWHERETABLE_NAME='INVENTORIES'ANDCOLUMN_NAME='QUANTITY_ON_HAND'ORDERBYENDPOINT_NUMBER;
ENDPOINT_NUMBER ENDPOINT_VALUE--------------- --------------0 01 272 423 574 745 986 1237 1498 1759 20210 353在示例13-1查询输出中，一行（1-10）对应于直方图中的每个桶。Oracle数据库向该直方图添加了特殊的第0个数据桶，因为第1个数据桶（27）中的值不是quantity_on_hand列的最小值。第0个桶的最小值为0 quantity_on_hand。3、frequency histogram
在frequency histogram中，列的每个值对应于直方图的单个桶。每个桶包含此单个值的出现次数。例如，假设36行包含列的值1 warehouse_id。端点值1具有端点号36。数据库在以下条件下自动创建频率直方图，而不是高度平衡的直方图：不同值的数量小于或等于指定的直方图桶数（最多254个）。每个列值重复一次。您可以使用USER_TAB_HISTOGRAMS视图查看频率直方图，如示例13-2所示。BEGINDBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'OE',TABNAME=>'INVENTORIES',METHOD_OPT=>'FOR COLUMNS SIZE 20 warehouse_id');END;/
SELECTCOLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,HISTOGRAMFROMUSER_TAB_COL_STATISTICSWHERETABLE_NAME='INVENTORIES'ANDCOLUMN_NAME='WAREHOUSE_ID';
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM------------------------------ ------------ ----------- ---------------WAREHOUSE_ID 9 9 FREQUENCY
SELECTENDPOINT_NUMBER,ENDPOINT_VALUEFROMUSER_TAB_HISTOGRAMSWHERETABLE_NAME='INVENTORIES'ANDCOLUMN_NAME='WAREHOUSE_ID'ORDERBYENDPOINT_NUMBER;
ENDPOINT_NUMBER ENDPOINT_VALUE--------------- --------------36 1213 2261 3370 4484 5692 6798 7984 81112 9在例13-2中，第一个桶为warehouse_id1。该值在表中显示36次，如以下查询所证实：oe@PROD> SELECT COUNT(*) FROM inventories WHERE warehouse_id = 1; COUNT(*)---------- 365、练习4、直方图优化练习
统计已销户用户数量，请优化以下语句select count(1) from ht.c_cons where status='close';SQL>selectstatus,count(1)fromht.c_consgroupbystatus;
STATUSCOUNT(1)------------------------------------------------------------ ----------close 19open 9519creating 462
SQL>createindexht.idx_c_cons_statusonht.c_cons(status);SQL>colownerfora10coltable_namefora20colcolumn_namefora20coldata_typefora30colhistogramfora20selectowner,table_name,column_name,data_type,column_id,num_distinct,histogram,NUM_NULLS,LAST_ANALYZEDfromdba_tab_columnswheretable_name='C_CONS'andowner='HT'orderbycolumn_id;SQL>SQL>SQL>SQL>SQL>2 3 4
OWNER TABLE_NAMECOLUMN_NAME DATA_TYPE COLUMN_ID NUM_DISTINCT HISTOGRAM NUM_NULLS LAST_ANALYZED---------- -------------------- -------------------- ------------------------------ ---------- ------------ -------------------- ---------- ------------------------------HT C_CONSCONS_NO NUMBER 1 10000 NONE 0 20-AUG-17HT C_CONSCONS_NAMEVARCHAR2 2 5057 NONE 0 20-AUG-17HT C_CONSORG_NAMEVARCHAR2 3 12 NONE 0 20-AUG-17HT C_CONSBUILD_DATEDATE 4 10000 NONE 0 20-AUG-17HT C_CONSSTATUSVARCHAR2 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>colownerfora10SQL>coltable_namefora20colcolumn_namefora20coldata_typefora30colhistogramfora20selectowner,table_name,column_name,data_type,column_id,num_distinct,histogram,NUM_NULLS,LAST_ANALYZEDfromdba_tab_columnswheretable_name='C_CONS'andowner='HT'orderbycolumn_id;SQL>SQL>SQL>SQL>2 3 4
OWNER TABLE_NAMECOLUMN_NAME DATA_TYPE COLUMN_ID NUM_DISTINCT HISTOGRAM NUM_NULLS LAST_ANALYZED---------- -------------------- -------------------- ------------------------------ ---------- ------------ -------------------- ---------- ------------------------------HT C_CONSCONS_NO NUMBER 1 10000 NONE 0 20-AUG-17HT C_CONSCONS_NAMEVARCHAR2 2 5057 NONE 0 20-AUG-17HT C_CONSORG_NAMEVARCHAR2 3 12 NONE 0 20-AUG-17HT C_CONSBUILD_DATEDATE 4 10000 NONE 0 20-AUG-17HT C_CONSSTATUSVARCHAR2 5 3 FREQUENCY 0 20-AUG-17SQL>selectcount(1)fromht.c_conswherestatus='open';Execution Plan----------------------------------------------------------Plan hashvalue:2016425671
-------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|6|8(0)|00:00:01||1|SORT AGGREGATE||1|6||||*2|INDEXFAST FULL SCAN|IDX_C_CONS_STATUS|9639|57834|8(0)|00:00:01|-------------------------------------------------------------------------------------------
Predicate Information(identifiedbyoperation id):---------------------------------------------------2-filter("STATUS"='open')Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 28 consistent gets 0 physical reads 0 redosize527 bytes sent via SQL*Nettoclient523 bytes received via SQL*Netfromclient 2 SQL*Net roundtripsto/fromclient 0 sorts(memory) 0 sorts(disk) 1rowsprocessedSQL>SQL>selectcount(1)fromht.c_conswherestatus='close';Execution Plan----------------------------------------------------------
Plan hashvalue:2292286995
---------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|6|1(0)|00:00:01||1|SORT AGGREGATE||1|6||||*2|INDEXRANGE SCAN|IDX_C_CONS_STATUS|24|144|1(0)|00:00:01|---------------------------------------------------------------------------------------
Predicate Information(identifiedbyoperation id):---------------------------------------------------
2-access("STATUS"='close')
Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redosize526 bytes sent via SQL*Nettoclient523 bytes received via SQL*Netfromclient 2 SQL*Net roundtripsto/fromclient 0 sorts(memory) 0 sorts(disk) 1rowsprocessed
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
