Oracle多列统计信息

0    174    1

Tags:

👉 本文共约2731个字,系统预计阅读时间或需11分钟。

简介

Oracle优化器对于基数值的估算是否准确关系到能否生成最优的执行计划,而基数值估算的准确性又取决于SQL中各个对象的统计信息是否完整、是否能真实反映出对象的数据分布情况。因此使用何种方法收集统计信息是很有讲究的:对于数据倾斜度较大的表需要收集直方图,在此基础上如果有多个列存在相关性,那么多列统计信息(也叫扩展统计信息)收集又是一个更好的选择。

在一般情况下,SQL语句的WHERE子句后面针对单张表都有多个条件,也就是根据多列的条件筛选得到数据。默认情况下,Oracle会把多列的选择率(Selectivity)相乘从而得到WHERE语句的选择率,但是这样有可能造成选择率不准确,从而导致优化器做出错误的判断。为了能够让优化器做出准确的判断,从而生成准确的执行计划,Oracle在11g数据库中引入了收集多列统计信息。多列统计信息包含列组统计信息(Column Group Statistics)和表达式的统计信息(Expression Statistics)。

使用程序包DBMS_STATS中的新函数CREATE_EXTENDED_STATS创建一个虚拟列,然后对表收集统计信息。如下所示,定义了两个扩展列:

以上SQL是对TEST用户下的T表,分别基于表达式和基于多列创建虚拟列,下次再收集表的统计信息时,将会自动收集到多列统计信息。需要注意的是,不能对SYS用户下的表创建扩展的统计信息,否则会报错“ORA-20000: Unable to create extension: not supported for SYS owned table”。

使用Oracle自带的DBMS_STATS包提供的存储过程DROP_EXTENDED_STATS来删除扩展统计信息:

定义扩展统计信息也可以直接在包DBMS_STATS中指定METHOD_OPT,收集统计信息时,把列组合作为单独列使用,如下所示:

在视图DBA_STAT_EXTENSIONS中,可以看到在数据库中定义的扩展统计信息:

当不清楚需要创建哪些列的扩展统计信息时,可以针对一个表,基于特定的工作负荷,通过使用DBMS_STATS.SEED_COL_USAGE和REPORT_COL_USAGE来确定需要哪些列组。需要注意的是,这种技术不适用于包含表达式列的统计工作。主要过程如下所示:

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
Oracle多列统计信息后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
验证码:
请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦。
  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部