oracle的索引类型及sql优化总结（一）

从大的方面来说，一般从业务上和技术上（oracle索引）来优化。

本文重点总结下oracle技术层面的优化：

索引类型的选择，索引列的选择，表之间的联结类型对于实现最优性能有着很高的重要性。

从算法维度索引广义可以分为：B-树索引（oracle索引所使用的结构）、位图索引、索引组织表。

         B-树索引：实现类似于倒置的树形结构，包括根节点，分支节点，叶子节点，并且使用树遍历算法来搜索列值。叶子节点中包含一对值（索引值，行编号rowid），索引值对应索引键列，行编号则表示行在表中数据块中内存地址；分支节点包含叶子几点目录及存储在其中叶子节点的值范围；根节点包含分支节点目录以及这些分支节点所包含的值范围。

         位图索引：适用于不经常进行更新，插入，删除的列。它更适合具有较少唯一值的进行只读运算的数据仓库表（例如：性别列就是个很好的例子，只有男女这样较少的唯一值）。

         索引组织表：表本身被组织为一个索引（列值，逻辑行编号），所有的列存储在索引树自身中，使用主键来访问的数据行将只会包含索引访问；所有的列都可以通过索引结构来获取，从而避免了表访问，实现了访问次数的最小化。适合于下面的特点：1、数据行长度较短；2、大多使用主键列进行访问的表

分区索引：范围分区（局部索引和全局索引）和散列分区

局部分区索引：使用local关键字来建立，其分区边界与表相同。简单来说，每个表分区联结的有一个索引分区。如果sql语句声明了分区键列上的谓语，执行计划就仅需要访问一个或很少索引分区；如果执行计划在最少分区中搜索，性能就会得到提升。

全局分区索引：使用global关键字来创建，起索引分区与表分区边界不一定要匹配，并且表和索引的分区键也可以不一样。对全局分区索引的维护需要获得表较高等级的锁，从而降低了应用的可用性。相反，对于局部索引的维护可以只在分区上完成，只影响相应的表分区。

注意：范围分区需要定期创建新的分区。

散列索引：对分区索引键列的值使用散列算法进行散列化来确定存储数据行的分区。这种方案适合顺序生成值的列，因为散列值相对是均匀分布的，每个分区数据行总数基本相等。

注意：1、散列分区不需要定期创建新的分区，开始总的分区数就确定了，后面的新增数据会散列分区到各个分区。

            2、散列分区表和索引在应对由唯一索引和主键索引所引起的与并发性相关的性能问题时是非常有效的。因为基本上，索引的当前最右叶子块将会是最主要的资源争夺点，通过散列分区，将最近的数据散列到了各个分区上。

           3、分区键值函数：ora_hash(column_name, 31, 0)将会返回分区ID。

什么时候使用索引？？

答：sql语句中声明的谓语是选择性的，即通过谓语查询将会获得很少的数据行，那么基于索引的访问路径性能将会更好。

        典型的基于索引的访问路径通常包含下面3个步骤：

       a、遍历索引树并在将sql语句中的谓语应用到索引列后收集叶子块的行编号（rowid）

       b、使用行编号从表中数据块中获取数据行。

       c、在所获取的数据行上应用其余的谓语来得出最终结果集。

索引访问一定比全表扫描效率高？？

答：不一定，如果sql语句的谓语查询返回了大量的数据行的情况下，有可能全表扫描效率更高；因为索引访问一般要先读取索引块，然后再获取数据块（单数据块）；而全表扫描就直接多块读取（多数据块）；在返回大量数据行的情况下，索引扫描可能比全表扫描读取的次数更多，IO次数更多；

       全表扫描和快速全扫描进行多块读取调用，而索引范围扫描或索引唯一扫描进行单块读取。多块读取比逐块读取效率高的多。

列的选择：

1、某一列上使用的了等式或范围谓语，对这一列建索引是个好的策略；对于多列索引，引导列应该是大多数谓语中被使用的列；

2、列的选择度的考虑；例如：某一列只有2个唯一值且是均匀分布的，就不适合建B-树索引；如果某一列有2个唯一值但不均匀分布，有一个值仅在少数数据行中出现，且sql查询中经常用到，这种情况，这一列可以考虑建索引；、

3、索引成本的考虑；某一列建了索引，那么插入，删除，更新都需要维护索引（更新索引）；

4、索引的大小；索引列的长度越长，索引也就越大；索引的成本可能超过索引带来的好处；

oracle的sql优化注意点：

1、空值不存储在某个单独列的索引上，因此谓语is null将不会使用单列索引（但空值是可以存储在多列索引中；通过使用另一个虚拟列来创建多列索引，就可以再is nul子句中启用索引）；

2、在某一索引列上使用了函数，这一列的索引就不会被选用（最好是在等号的右边使用函数，避免在索引列上使用函数）;

3、通过oracle的hint来改变执行计划；

4、优化索引类型的选择，索引列的选择，及表间的关联。