ORACLE创建管理索引

 ORACLE索引管理需要遵从一系列的指导原则，包含以下内容：

    插入表数据之后再创建索引

    针对合理的表和列创建索引

    对索引列的顺序进行排列来提升性能

    限制每个表的索引的个数

    删除不再需要的索引

    估计索引的大小并指定存储参数

    为每个索引指定表空间

    采用并行的方式创建索引

    考虑创建索引是使用NOLOGGING参数

    权衡进行索引合并或重建的代价和带来的好处

    考虑对索引进行失效操作和删除索引的代价

针对合理的表和列创建索引

根据以下指导原则决定在什么情况下需要创建索引：

• 当需要经常读取一个较大的表中少于15%的数据的时候可以考虑创建索引。这一百分比门限值会随表的扫描速度和行数据的散列方式与索引键之间的关系而有较大的差别。表的扫描速度越快，则百分比取值越低；the more clustered the row data，则百分比门限取值越高。
• 为了改进多表关联的查询性能，对关联的列进行索引。
• 小表不需要索引。如果查询的时间很长，则表有可能已经变大了。
• 强烈建议对某些列建立索引，具有以下特征的列可建立索引：
• 该列中每行的值是唯一的。
• 该列中的值取值范围非常大（使用常规索引较好）。
• 该列中的值取值范围非常小（使用bitmap索引较好）。
• 该列中包含有许多空值，但是查询经常选择所有有值的行。在这种情况下，使用以下的语句：WHERE COL_X > -9.99 * power(10,125)。上述的语句更好的形式为：WHERE COL_X IS NOT NULL。这是因为第一种表达方式使用到了COL_X的索引（假设COL_X是一个数字类型的列）。
• 具有以下特征的列则不太适合使用索引：
• 该列中有很多空值，但是并不搜索值为非空的行。
• LONG和LONG RAW类型的列不能进行索引。

    单条索引记录的大小不能超过数据块中可用空间的（大概）1.5倍。

对索引列的顺序进行排列来提升性能

    CREATE INDEX语句中的列的顺序会影响查询的性能。通常情况下，首先指定最常使用的列。
    例如：针对多个列创建一个索引，列1、列2、列3，单独访问列1和同时访问列1、列2的查询速度会得到提高，而单独访问列2、单独访问列3、以及同时访问列2、列3的查询不会使用索引。

限制每个表的索引的个数

    一个表可以有任意数量的索引。但是索引越多的话，那么在对表进行修改的时候所需的开销也就更大。当插入和删除行的时候，表上的所有索引都要被更新，当某个列被更新的时候，所有包含该列的索引也都需要被更新。
    所以，在“获取数据的速度”和“更新表的速度”之间需要进行平衡。如果某个表基本上是“只读”的话，那么建立多个索引是合理的，如果某个表需要经常被更新的话，少建索引更为合理。

删除不再需要的索引

    在以下情况下，考虑删除索引：

•     对查询加速不起作用。例如：表非常小；表中的记录数非常多，但是索引的条目数却非常少。
•     程序中的查询不使用该索引。

    在重建索引之前必须先删除索引。

估计索引的大小并指定存储参数

    在创建索引之前估算索引的大小有利于更好的进行磁盘空间的规划和管理。综合考虑索引、表、UNDO表空间、重做日志的大小，来决定容纳整个数据库所需要的磁盘空间。基于以上估算，可以进行合理的硬件资源分配和做出其他相关的策略决定。
    利用估算出的某个索引的大小可以更好的管理该索引所使用的磁盘空间。在估算好该索引所需要占用的最大空间的前提下，创建索引时可以通过指定合理的存储参数来提升I/O和使用该索引的程序的效率。指定存储参数后，表数据段会分配到更少的extent，而所有的索引数据被存储在彼此相关的连续的磁盘区域中，这样就减少了使用到该索引的查询所需的磁盘I/O的时间。
    单条索引记录的最大空间大致为数据块大小的1.5倍。

为每个索引指定表空间

    索引可以被创建在任何表空间中。既可以和被索引的表在相同的表空间中，也可以和被索引的表处于不同的表空间中。对于前一种情况，对于数据库的维护来说会更为方便，也有利于保证程序应用的可用性，因为所有相关的数据的在线/离线状态完全保持已知。
    将表和它的索引存储在不同的表空间当中会产生更好的性能表现，这样会减少磁盘竞争。但是当其中的一个表空间处于离线状态的话，那么引用了这张表的语句就不会正常执行了。

采用并行的方式创建索引

    索引也是可以并行创建的，就像并行创建表一样。因为多个后台进程同时进行索引的创建工作，所以与单个进程的顺序索引创建相比，数据库能够更快的创建索引。
    并行创建索引的时候，每个进程是单独使用存储参数的。例如：如果某个创建索引的命令中INITIAL参数的值为5M，而并行度为12的话，那么这一索引创建操作最少消耗60M的存储空间。

考虑创建索引是使用NOLOGGING参数

    在CREATE INDEX语句中可以使用NOLOGGING参数来使重做日志产生最少的记录。但是需要注意的是：使用NOLOGGING参数创建的索引不会被归档，所以在创建索引之后需要进行一次备份。
    使用NOLGGING模式创建索引有如下好处：

•     节省重做日志的空间
•     创建索引的时间会比缩短
•     提升大索引并行创建的性能

    一般来说，使用NOLOGGING模式创建索引对创建工作性能提升的效果，大的索引的效果会比小索引创建的效果提升更为明显。创建小索引的时候使用NOLOGGING参数对创建索引使用的时间影响很小，创建大索引的时候效果特别明显，特别是当并行创建的时候更是如此。

权衡进行索引合并和重建的代价及益处

    索引空间大小分配不合理或加速增长会产生索引碎片。通过重建和合并索引能够消除或者减少索引碎片。但是当执行以上两种操作中的其中一种之前，一定要对其带来的益处和代价进行权衡，并选择最为适宜当前情况的操作。
 
    下面的表格对重建和合并索引的利弊进行了比较：

重建索引	合并索引
很快的将索引移动到另一个表空间	不能将索引移动到另一个表空间
代价较大：需要更多的磁盘空间	代价较小：不需要额外的磁盘空间
创建新的索引树，会适当收缩树的高度	合并索引树中同枝的叶节点块
无需删除原有索引就可以快速更改存储和表空间参数。	快速释放索引叶节点块以供使用

 

    在有B-tree叶节点块可供合并被释放的情况下，可以使用下面的语句来合并叶节点块：
    ALTER INDEX vmoore COALESCE。
       下图说明了上述语句针对索引树的叶子节点的合并及释放的过程：

考虑删除索引和对索引进行失效操作的代价

    由于唯一键和主键拥有相关联的索引，所以当考虑是否disable或drop一个UNIQUE或PRIMARY KEY的时候，需要将删除和创建索引的代价也做为考虑的一个因素。如果相关联的索引非常巨大，那么最好是使这个约束保持enable的状态，这样会比较节省时间。在drop和disable一个UNIQUE KEY或PRIMARY KEY的时候，也可以显式的指出是想保留还是删除相关的索引。