专栏首页小麦苗的DB宝专栏【DB笔试面试636】在Oracle中,如何收集直方图信息?在收集直方图时有哪些注意事项?

【DB笔试面试636】在Oracle中,如何收集直方图信息?在收集直方图时有哪些注意事项?

题目部分

在Oracle中,如何收集直方图信息?在收集直方图时有哪些注意事项?

答案部分

默认情况下,数据库会为列收集基本统计信息,但不会收集直方图信息。Oracle通过指定DBMS_STATS的METHOD_OPT参数来创建直方图。METHOD_OPT参数可以接受如下的输入值:

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size_clause] column|attribute [size_clause] [,column|attribute [size_clause]...]

其中的size_clause必须符合如下的格式:

SIZE {integer | REPEAT | AUTO | SKEWONLY}

含义如下所示:

l SKEWONLY:只对数据分布不均衡的列收集直方图统计信息。

l REPEAT:只对己经有直方图统计信息的列收集直方图统计信息。

l AUTO:让Oracle自行决定是否对目标列收集直方图统计信息,以及使用哪种类型的直方图。

l integer:直方图的Bucket的数量,必须是在1~254的范围内,1表示删除该目标列上的直方图统计信息。

METHOD_OPT参数的默认值为“FOR ALL COLUMNS SIZE AUTO”,“FOR ALL COLUMNS SIZE 1”表示删除所有列直方图统计信息。下面是一些常用的收集方法:

l 对T表上所有有索引的列以自动收集的方式收集直方图:FOR ALL INDEXED COLUMNS SIZE AUTO

l 对T表上的列A和列B以自动收集的方式收集直方图:FOR COLUMNS SIZE AUTO A B

l 对T表上的列A和列B收集直方图统计信息,同时指定BUCKET数量均为10:FOR COLUMNS SIZE 10 A B

l 对T表上的列A和列B收集直方图统计信息,同时指定列A的BUCKET数量为10,列B的BUCKET数量为5:FOR COLUMNS A SIZE 10 B SIZE 5

l 只删除表T上列A的直方图统计信息:FOR COLUMNS A SIZE 1

l 删除表T上所有列的直方图统计信息:FOR ALL COLUMNS SIZE 1

如果需要删除某个列SAL的直方图信息,在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,表示删除列的基本统计信息和直方图信息。

SKEWONLY的示例如下:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME           => '',
                                TABNAME          => '',
                                ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                METHOD_OPT       => 'for all columns size skewonly',
                                CASCADE          => TRUE,
                                DEGREE           => 7);
END;

其中,DEGREE指定了并行度视主机的CPU个数而定,ESTIMATE_PERCENT指定了采样比率,此处使用了AUTO目的是让Oracle来决定采样收集的比率,绘制直方图时会根据采样的数据分析结果来绘制,当然也可以人为指定采样比率。如:ESTIMATE_PERCENT=>20指定采样比率为20%,CASCADE=>TRUE指定收集相关表的索引的统计信息,该参数默认为FALSE,因此使用DBMS_STATS收集统计信息时默认是不收集表的索引的统计信息的。

关于直方图的一些注意事项:

① 对于超过32个字符的字符型列,超出的那一部分无法在直方图中体现,这种先天性的缺陷会直接影响CBO对相关文本型字段的可选择率及返回结果集的Cardinality的评估。

② 数字和日期在直方图上被精确表示。

③ 如果目标列的数据是均匀分布的(例如,主键列、唯一索引列),那么就不需要对这些列收集直方图统计信息。

④ 对于那些从来没有在WHERE条件中出现过的列,无论其数据分布是否均匀,都无须对这些列收集直方图统计信息。

⑤ 如果某个列从未在WHERE条件中出现过,那么SYS.COL_USAGE$中就不会有这个列的使用记录,那么Oracle在以默认方式自动收集统计信息时就不会收集这个列的直方图统计信息。

⑥ 如果目标列的DISTINCT值的数量和目标表的记录数据量相同,即使该目标列在SYS.COL_USAGE$中有使用记录,那么Oracle在自动收集直方图统计信息的时候也不会对该列收集直方图统计信息。

⑦ Oracle是怎么来判断某列的数据分布是否是倾斜的呢?Oracle采用了一种很简单的方法来判断某列是否倾斜,就是判断目标列的DISTINCT值的数量是否和目标表的记录数量相同,如果相同,那么Oracle就认为该列的数据分布不是倾斜的,否则就是倾斜的。

⑧ 如果目标列的DISTINCT值的数量和目标表的数据量相同(即数据分布不倾斜),即使该目标列在SYS.COL_USAGE$中有使用记录,那么Oracle在自动收集直方图统计信息的时候也不会对该列收集直方图统计信息。

⑨ “在手工收集直方图统计信息的时候,如果手工指定的Bucket的数量等于目标列的DISTINCT值的数量,且这个值是小于等于254的话,那么Oracle此时收集的直方图统计信息的类型应该是FREQUENCY”——这个结论成立的前提条件是该列的数据分布是倾斜的。

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

本文分享自微信公众号 - DB宝(xiaomaimiaolhr),作者:小麦苗best

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-09-09

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 【DB笔试面试634】在Oracle中,什么是直方图(Histogram)?直方图的使用场合有哪些?

    在Oracle数据库中,CBO会默认认为目标列的数据在其最小值(LOW_VALUE)和最大值(HIGH_VALUE)之间是均匀分布的,并且会按照这个均匀分布原则...

    小麦苗DBA宝典
  • 【DB笔试面试635】在Oracle中,直方图分为哪几类?

    Oracle数据库里的直方图使用了一种称为Bucket(桶)的方式来描述目标列的数据分布。Bucket(桶)是一个逻辑上的概念,相当于分组,每个Bucket就是...

    小麦苗DBA宝典
  • 【DB笔试面试645】在Oracle中,当收集表的统计信息时应该注意哪些问题?

    ① 对于数据量不大的OLTP类型的系统,建议使用自动收集统计信息,并对一些特殊的大表写JOB定时收集统计信息。如果是数据量很大的OLAP或者DSS系统,那么建议...

    小麦苗DBA宝典
  • 任意半径中值滤波(扩展至百分比滤波器)O(1)时间复杂度算法的原理、实现及效果。

    主要参考论文:Median Filter in Constant Time.pdf

    用户1138785
  • 机器视觉算法(第13期)----直方图处理中的两大神器!

    上期我们一起揭开了图像处理中的卷积操作的疑惑, 机器视觉算法(第12期)----图像处理中的卷积操作真的是在做卷积吗? 今天,我们一起看下直方图处理中的两大神器...

    智能算法
  • 深入理解MySQL8.0直方图

    墨墨导读:MySQL 8.0 新功能直方图,继承于Oracle ,MairaDB的实现方式。本文从MySQL角度解释,直方图是什么。

    数据和云
  • 【DB笔试面试634】在Oracle中,什么是直方图(Histogram)?直方图的使用场合有哪些?

    在Oracle数据库中,CBO会默认认为目标列的数据在其最小值(LOW_VALUE)和最大值(HIGH_VALUE)之间是均匀分布的,并且会按照这个均匀分布原则...

    小麦苗DBA宝典
  • MYSQL 8 Histogram statistics 直方图,开始开挂的MYSQL

    周六日,松懈了,罪过罪过, MYSQL 从8.0开始就开始正式走到开挂数据库得行列,估计8.0铺开后,大部分原先的MYSQL的经验的进行一次洗牌,今天就从MYS...

    AustinDatabases
  • C++ OpenCV直方图计算

    上述直方图概念是基于图像像素值,其实是对图像梯度,每个像素的角度、等一切图像的属性值,我们都可以建立直方图。这个才是直方图的概念的真正意义,不过是基于图像像素灰...

    Vaccae
  • 【从零学习OpenCV 4】直方图匹配

    经过几个月的努力,小白终于完成了市面上第一本OpenCV 4入门书籍《从零学习OpenCV 4》。为了更让小伙伴更早的了解最新版的OpenCV 4,小白与出版社...

    小白学视觉

扫码关注云+社区

领取腾讯云代金券