基于Oracle的SQL优化教程

oracle里的优化器

oracle里的优化器分为rbo和cbo两种类型，rbo是基于规则的优化器，cbo是基于成本的优化器。

oracle的sql语句执行过程：

基于规则的优化器

oracle会在代码里事先给各个类型的执行路径定一个等级，从等级1到等级15，等级越低执行效率越快。等级1对应的执行路径是“single row by rowid（通过rowid来访问单行数据）”，等级15对应的执行路径是“full table scan（全表扫描）”。

当目标sql有两条或者两条以上的执行路径等值相同时，我们可以通过调整相关对象在数据字典缓存中的缓存顺序来影响rbo对其执行计划的选择；当目标的sql中出现多表连接的情况rbo会按照从右到左的顺序来决定谁是驱动表谁是被驱动表，进而选择执行计划。所以还可以通过改变目标sql中涉及的各个对象在sql文本中的先后顺序，来改变表连接的驱动表和被驱动表，进而调整sql的执行计划。

当目标sql有两条或者两条以上的执行路径等值不相同时，那么无论怎么调整相关对象在sql文本中的顺序都不会对执行计划起作用。

基于成本的优化器

oracle里的成本是对执行目标sql所耗费的i/o、cpu和网络资源的一个估算值。

集的势：cardinality，指指定集合所包含的记录数。即指定结果集的行数。表示对目标sql的某个具体执行步骤的执行结果所包含的记录数的估算。当然，如果是针对整个目标sql，那么此时的cardinality就表示对该sql最终执行结果所包含的记录数的估算。某个执行步骤的对应cardinality值越大，那么所对应的成本值往往也就越大，这个执行步骤所在执行路径的总成本值也就会越大。可选择率：selectivity，指施加指定谓词条件后返回结果集的记录数占未施加任何谓词条件的原始结果集的记录数的比率。可选择率的值越大，就意味着返回结果集的cardinality的值就越大，所以估算出来的成本值也就会越大。

cbo优化器的可传递性：cbo会对原目标sql做简单的等价改写，这样可以提供更多的执行路径给cbo做选择。

1、简单谓词传递 ，t1.c1=t2.c1 and t1.c1=10，oracle会自动将t2.c1=10的条件添加。

2、连接谓词传递，t1.c1=t2.c1 and t2.c1=t3.c1，oracle会自动将t1.c1=t3.c1的条件添加。

3、外连接谓词传递，t1.c1=t2.c1(+) and t1.c1=10，oracle会自动将t2.c1(+)=10的条件添加。