基于Oracle的SQL优化--学习（七）

最新推荐文章于 2022-08-23 08:45:20 发布

最新推荐文章于 2022-08-23 08:45:20 发布 · 244 阅读

Oracle 专栏收录该内容

24 篇文章

订阅专栏

本文介绍了Oracle数据库中SQL执行计划的解析方法，包括执行顺序、常见执行计划关键字及其含义，如全表扫描、索引扫描、表连接等，并讨论了位图索引的特点及应用场景。

如何查看执行计划的执行顺序

查看执行顺序的口诀是：先从最开头一直连续往右看，直到看到最右边的并列的地方；对于不并列的，靠右的先执行；如果见到并列的，就从上往下看，对于并列的部分，靠上的先执行。

对于复杂的SQL执行的语句，可以使用XPLAN包，包的源码的地址是：

http://www.dbsnake.net/wp-content/uploads/2012/08/xplan.sql_.txt

Oracle中常见的执行计划

与表访问相关的执行计划

Oracle中与表访问有关的两种方法：全表扫描和ROWID扫描。反应在执行计划上，与全表扫描对应的执行计划中的关键字是“TABLE ACCESS FULL”，与ROWID对应的执行计划中的关键字是“TABLE ACCESS BY ROWID”或“TABLE ACCESS BY INDEX ROWID”。如果ROWID来源于用户手工指定的话执行计划的关键字是“TABLE ACCESS BY ROWID”，如果来源于索引则是“TABLE ACCESS BY INDEX ROWID”。

与B树索引相关的执行计划

Oracle中B树索引访问相关的方法是：索引唯一扫描、索引范围扫描、索引全扫描、索引快速全扫描和索引跳跃式扫描．反映在执行计划上与索引唯一扫描对应的关键字是“ INDEX UNIQUE SCAN ",与索引范围扫描对应的关键字是“ INDEX RANGE SCAN"，与索引全扫描对应的关键字是“ INOEX FULL SCAN "，与索引快速全扫描对应的关键字是“INDEX FAST FULL SCAN " ,与索引跳跃式扫描对应的关键字是"INDEX SKIP SCAN"

与位图索引相关的执行计划

Oracle的位图索引的物理存储结构是：<被索引的键值，对应的rowid下限，对应的rowid上限,位图段>。这里位图段是被压缩存储的，解压缩后就是一连串 0 和 l 的二进制位图序列，其中 l 表示被索引键值的一个有效 rowid ,Oracle通过一个转换函数将解压缩后的位图段中的 l 结合对应 rowid 的上下限，转换为被索引键值所对应的有效 rowid．

Oracle数据库中位图索引的锁的粒度是在索引行的位图段上。对干 Oracle数据库中的位图索引而言，它是没有行锁这个概念的，要锁就锁索引行的整个位图段，而多个数据行可能对应同一个索引行的位图段。这种锁的粒度就决定了位图索引不适用于高并发且频繁修改的 OLTp 系统，如果在高并发且绷繁修改的 OLTp 系统中使用了位图索引，很可能会导致严重的并发问题，甚至会产生死锁。

与 B 树索引相比，位图索引的优势主要体现在如下几个方面：

( l ）因为位图索引的位图段是服后存储的，所以如果被索引的列的 diatind 位较少，那么位图索引与相同列上的 B 树索引比起来，会显著节省存储空间。

( 2 ）如果需要在多个列上创建索引，那么位图索引与同等条件下的 B 树索引比起来，往往会显著节省存储空间。

( 3 ）位图索引能够快速处理一些包含了各种 ANO 或 OR 查询条件的 SQL ，这主要是因为位图索引能够实现快捷的按位运算的缘故。

Oracle 数据库里常见的与位图索引访问相关的方法包括如下这些类型：位图索引单键值扫描、位图索引范围扫描、位图索引全扫描、位图索引快速全扫描、位图按位与、位图按位或、位图按位减等。反映在执行计划上，与位图索引单键值扫描对应的关键字是“ BITMAP INDEX SINGLE VALUE " ，与位图索引范围扫描对应的关键字是“ BITMAP INDEX RANGE SCAN " ，与位图索引全扫描对应的关键字是“ BITMAP INDEX FULL SCAN " ，与位图索引快速全扫描对应的关链字是“ BITMAP INDEX FAST FULL SCAN " ，与位图按位与对应的关健字是“ BITMAP AND " ，与位图按位或对应的关键字是“ BITMAP OR " ，与位图按位减对应的关键字是“ BITMAP MINUS " . 另外需要注意的是，Oracle在使用完位图索引后通常会将最后的位图运算结果转化为 ROWID ，这一步转换过程对应的执行计划中的关键字是“ BITMAP CONVERSION TO ROWIDS "。

与表连接相关的执行计划

Oracle中与表连接相关的方法有排序合并连接、嵌套循环连接、哈希连接、反连接和半连接。反映在执行计划上，与排序合并连接对应的关键字是“ SORT JoIN ”和“ MERGE JOIN " ，与嵌套循环连接对应的关健字是“ NESTED LOOPS " 与哈希连接对应的关健字是“ HASH JOIN”，与反连接对应的关键字是“ ANTI "（依据所采用的不同的反连接方法，所对应的关键字可能是“ HASH J01N ANTI ”、“ MERGE JOIN ANTI ”或“ NESTED LOOPS ANTI " ) ．与半连接对应的关键字是“ SEMI " （依据所采用的不同的半连接方法，所对应的关键字可能是' HASH JOIN SEMI ”、“ MERGE JOIN SEMI ”或“ NESTED L00PS SEMI " ）。

其他的典型的执行计划

AND-EQUAL（INDEX MERGE）

AND-EQUAL又称为INDEX MERGE，顾名思义，INDEX MERGE 就是指如果 where 条件里出现了多个针对不同单列的等值条件，并且这几列都有单键值的索引，则 oracle 可能会以相应的单个等值条件去分别扫描这些索引，然后 Oracle 会合并这些扫描单个索引所得到的 rowid 集合，如果能从这些集合中找到值相同的 rowid ，那么这个 rowid 就是目标 SQL 最终执行结果所对应的 rowid记录。最后Oracle只需用这些 rowid 回表就能得到目标SQL执行的结果。

INDEX JOIN

INDEX JOIN指的是针对单表上的不同索引之间的连接，在执行计划中对应的关键字与普通的表连接一样，只不过参与连接的对象不是表而是索引。

VIEW

Oracle 在处理包含视图的 SQL 时，根据该视图是否能做视图合并，其对应的执行计划有如下两种形式：

1、如果可以做视图合并则 Orade 在执行该 SQL 时可以直接针对该视图的基表，此时 SQL 的执行计划中很可能不会出现关键字“ VIEW" （不能完全依赖关键字“ VIEW ”的出现与否来判断 Oracle是否做了视图合并，因为对于某些 SQL 而言，即使 oracle 已经做了视图合并但其所对应的执行计划中可能还是会显示关键字’VIEW" )。

2、如果不能做视图合并，则 Oracle 将把该视图看作一个整体并独立地执行它，此时 SOL 的执行计划中将会出现关
键字“ VIEW ”。

FILTER

FILTER 直译过来就足过滤、筛选的意思．它是一种特殊的执行计划，所对应的执行过程就是如下三步：

( l ）得到一个驱动结果集。

( 2 ）根据一定的过滤条件从上述驱动结果集中滤除不满足条件的记录。

( 3 ）结果集中剩下的记录就会返回给最终用户或者继续参与下一个执行步骤。

SORT

执行计划中的SORT通常会以组合的方式出现，这些组合方式包括但不限于如下几种：

（1）SORT AGGREGATE

（2）SORT UNIQUE

（3）SORT JOIN

（4）SORT GROUP BY