Oracle索引梳理系列（十）- 直方图使用技巧及analyze table操作对直方图统计的影响（谨慎使用）
发布时间：2020-07-21 08:44:13 所属栏目：Oracle 来源：互联网
导读：版权声明：本文发布于http://www.cnblogs.com/yumiko/，版权由Yumiko_sunny所有，欢迎转载。转载时，请在文章明显位置注明原文链接。若在未经作者同意的情况下，将本文内容用于商业用途，将保留追究其法律责任的权利。如果有问题，请以邮箱方式联系作者（7931
|
版权声明：本文发布于http://www.cnblogs.com/yumiko/，版权由Yumiko_sunny所有，欢迎转载。转载时，请在文章明显位置注明原文链接。若在未经作者同意的情况下，将本文内容用于商业用途，将保留追究其法律责任的权利。如果有问题，请以邮箱方式联系作者（793113046@qq.com）。 前言
1、直方图概述
2、直方图的分类
3、直方图的优势对于ORACLE而言，CBO优化器可以根据直方图收集的列值分布信息，让选择性高（返回数据行比例少）的列值使用索引，而选择性低（返回数据行比例多）的列值不使用索引。尤其对于存在数据倾斜严重的列而言，直方图很重要。 注：数据倾斜，主要指某列上的一个数值，相较于该列其他数值，出现比例高，如：“性别”列，“男性”占到该列整体数值（男性、女性）的80%，存在明显的数据倾斜现象。 4、直方图适用范围一般而言，直方图不受是否使用索引的限制，即可以用来统计索引列，也可以统计非索引列。但对于非索引列的统计，意义不大。 5、直方图涉及的主要视图直方图类型的视图：DBA_TAB_COL_STATISTICS，USER_TAB_COL_STATISTICS，ALL_TAB_COL_STATISTICS 直方图具体信息的视图：DBA_TAB_HISTOGRAMS，USER_TAB_HISTOGRAMS，ALL_TAB_HISTOGRAMS 6、直方图对于执行计划选择影响的示例说明本示例中使用的数据库版本为ORACLE 11.2.0.4。 首先，准备一张测试表TEST，其中OWNER列存在严重的数据倾斜，具体如下。 Yumiko_sunny@OA01> select distinct owner,count(*) as col_rows,2 (select count(*) from test) as tab_rows,3 to_char(round(count(*)/(select count(*) from test)*100,2),'90.99')||'%' 4 as data_ratio 5 from test group by owner; OWNER COL_ROWS TAB_ROWS DATA_RATIO -------------------- ---------- ---------- ---------- HR 476 535164 0.09% OE 1988 535164 0.37% ORDDATA 3598 535164 0.67% SCOTT 98 535164 0.02% SYS 529004 535164 98.85% 从上图中可以看到，该列的SYS值分布占到了整体的98%，表明存在严重的倾斜。 为OWNER列创建索引，并使用ANALYZE TABLE的方法收集统计信息。 --收集统计信息 在上面的方法中，虽然通过analyze table table_name compute statistics的方法，收集了表的统计信息，但并未收集直方图的信息。 这里先忽略，后面可以对比for all columns子句的情况再看下。 查看此时索引列执行计划的选择情况，这里以倾斜数据SYS为条件进行检索。 Yumiko_sunny@OA01> select * from test where owner='SYS';
529004 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3856466897
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107K| 10M| 1799 (1)| 00:00:22 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 107K| 10M| 1799 (1)| 00:00:22 |
|* 2 | INDEX RANGE SCAN | IND_TEST | 107K| | 228 (1)| 00:00:03 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
从上面返回的结果看，529K行的SYS数据，仅仅返回107K行，显然存在很大的误差。 此外，对于数据倾斜达到98%的SYS而言，显然全表扫描的效率应该更高，这里应该与错误的统计信息有关。 使用dbms_stats.gather_table_stats的方式再次收集表的统计信息。 Yumiko_sunny@OA01> exec dbms_stats.gather_table_stats('SCOTT','TEST',cascade=>true);
PL/SQL procedure successfully completed.
Yumiko_sunny@OA01> select table_name,2 to_char(LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
3 from dba_tables where TABLE_NAME='TEST';
TABLE_NAME LAST_ANALYZED
------------------------------ -------------------
TEST 2016-11-13 21:50:01
Yumiko_sunny@OA01> select column_name,histogram from dba_tab_col_statistics where table_name='TEST';
COLUMN_NAME HISTOGRAM
-------------------- ---------------
OWNER FREQUENCY
OBJECT_NAME NONE
SUBOBJECT_NAME NONE
OBJECT_ID NONE
DATA_OBJECT_ID NONE
OBJECT_TYPE NONE
CREATED NONE
LAST_DDL_TIME NONE
TIMESTAMP NONE
STATUS NONE
TEMPORARY NONE
COLUMN_NAME HISTOGRAM
-------------------- ---------------
GENERATED NONE
SECONDARY NONE
NAMESPACE NONE
EDITION_NAME NONE
从上图可以看到，此时完成了对表的最新统计，同时收集了索引列的直方图信息，且该直方图为”等频直方图“。 再次查看此时索引列的执行计划选择情况，这里分别以选择性差的倾斜数据SYS为条件，以及以选择性好的SCOTT为条件分别进行检索。 --以SYS为条件进行查询 如之前所说，通过直方图收集准确的数据分布信息， 对于选择性差的SYS值，CBO优化器采用了全表扫描的方式进行数据的访问 对于选择性好的SCOTT值，CBO优化器则采用了索引扫描的方式进行数据的访问 （编辑：鄂州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
