Oracle优化器 Cardinality基数与Selectivity选择性

最新推荐文章于 2024-03-12 21:58:59 发布

原创最新推荐文章于 2024-03-12 21:58:59 发布 · 752 阅读

0 ·

CC 4.0 BY-SA版权

SQL优化专栏收录该内容

3 篇文章

订阅专栏

本文深入解析Oracle数据库中的两种优化器：基于规则的优化器(RBO)和基于成本的优化器(CBO)。RBO通过预设等级选择执行路径，适用于OLTP场景；CBO则根据统计信息计算执行成本，选择成本最低的执行计划，更适用于复杂查询。

优化器（optimizer）是oracle数据库内置的一个核心子系统。优化器的目的是按照一定的判断原则来得到它认为的目标SQL在当前的情形下的最高效的执行路径，也就是为了得到目标SQL的最佳执行计划。依据所选择执行计划时所用的判断原则，oracle数据库里的优化器又分为RBO和CBO两种。

一、基于规则的优化器。《RBO: Rule-Based Optimization》

Oracle会在代码里事先为各种类型的执行路径定一个等级，一共15个等级，从等级1到等级15，oracle认为等级1的执行路径是效率最高的，等级15是执行效率最差的。对于等级相同的执行计划，oracle根据目标对象的在数据字典中缓存的顺序判断选择哪一种执行计划。RBO是一种适合于OLTP类型SQL语句的优化器。相对于CBO而言，RBO有着先天的缺陷，一旦SQL语句的执行计划出现问题，将很难调整。因此，从ORACLE 10g开始，RBO已被CBO所取代。

关于RBO的访问路径，官方文档做了详细介绍：

RBO Path 1: Single Row by Rowid

RBO Path 2: Single Row by Cluster Join

RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key

RBO Path 4: Single Row by Unique or Primary Key

RBO Path 5: Clustered Join

RBO Path 6: Hash Cluster Key

RBO Path 7: Indexed Cluster Key

RBO Path 8: Composite Index

RBO Path 9: Single-Column Indexes

RBO Path 10: Bounded Range Search on Indexed Columns

RBO Path 11: Unbounded Range Search on Indexed Columns

RBO Path 12: Sort Merge Join

RBO Path 13: MAX or MIN of Indexed Column

RBO Path 14: ORDER BY on Indexed Column

RBO Path 15: Full Table Scan

二、基于成本的优化器。《 CBO: Cost-Based Optimization》

CBO是一种比RBO更加合理、可靠的优化器，它是从ORACLE 8中开始引入，但到ORACLE 9i 中才逐渐成熟，在ORACLE 10g中完全取代RBO。 CBO选择执行计划时，以目标SQL成本为判断原则，CBO会选择一条执行成本最小的执行计划作为SQL的执行计划，各条执行路径的成本通过目标SQL语句所涉及的表、索引、列等的统计信息算出。这里的成本是oracle通过相关对象的统计信息计算出来的一个值，它实际上代表目标SQL对应执行步骤所消耗的IO、CPU、网络资源（针对于dblink下的分布式数据库系统而言）的消耗量，oracle会把网络资源的消耗量计算在IO成本内，实际上你看到的成本为IO、CPU资源，另外需要注意的是，oracle在未引入系统统计信息之前，CBO所计算的成本值实际全是基于IO计算的。

1、Cardinality（基数，集的势）

是Oracle预估的返回行数，即对目标SQL的某个具体执行步骤的执行结果所包含记录数的估算值。如果是针对整个目标SQL，那么此时的Cardinality就表示该SQL最终执行结果所包含记录数的估算值。例如，一张表T有1000行数据，列COL1上没有直方图，没有空值，并且不重复的值（Distinct Value）有500个。那么，在使用条件“WHERE COL1=”去访问表的时候，优化器会假设数据均匀分布，它估计出会有1000/500=2行被选出来，2就是这步操作的Cardinality。通常情况下，Cardinality越准确，生成的执行计划就会越高效。

2、Selectivity（可选择率）是指施加指定谓词条件后返回结果集的记录数占未施加任何谓词条件的原始结果集的记录数的比率。可选择率的取值范围显然是0～1，它的值越小，就表明可选择性越好。当可选择率为1时的可选择性是最差的。

Selectivity=1 / NUM_DISTINCT(目标列的DISTINCT的数量,此为最简单的计算可选择率的情况,在目标列上没有直方图,且没有NULL时的公式)

Cardinality=NUM_ROWS * Selectivity

---------------------------------------------------------------------------------------------------------

但Oracle在文档中，有时用基数来指一个操作返回的记录行数，有时也将基数指不重复的值的数量，因此基数与选择性在《SQL优化核心思想》中也有如下解释：

1、Cardinality（基数）某个列唯一键的数量（某列中不重复的值的数量，DISTINCT的数量），比如性别列，该列只有男女之分，所以这一列基数是2，基数越高与总行数越接近，数据分布可能会越均匀。

2、Selectivity（选择性）基数与总行数的比值再乘以100%，为某列的选择性（选择性大于20%，数据分布可能会越均衡）。