索引的设计规范

主键索引的规范

原则

对数据量表应该先在主键列建唯一索引，再建主键约束

衡量

分区表的主键必须采用该方法

方法

主键上隐含索引，drop或disable主键时，索引会丢失，为保证性能不变，为了对主键约束和相应索引有更多的控制，对大表(分区表)的索引采用如下方式建立：

(1)在准备建主键的列上建立唯一索引（UNIQUE INDEX）: CREATE UNIQUE INDEX Index_Name ON Table_Name(Column_Name) TABLESPACE  TBS_INDEX;

(2)再加上主键约束：

ALTER TABLE Table_Name ADD (PRIMARY KEY(Column_Name) USING INDEX TABLESPACE TBS_INDEX );

 Oracle会在指定的列上加上主键约束，并且使用该索引

实施

分区表的主键默认索引是全局索引，所以主键索引的分区方法：先建立分区化的唯一索引，再建主键约束，

 

4.2、唯一约束索引的规范

原则

针对大数据量表应该先在唯一约束列上建立普通索引，再添加唯一性约束

衡量

分区表的唯一约束必须采用该方法

方法

删除或禁用唯一性约束通常同时使相关联的唯一索引失效，因而降低了数据库性能。要避免这样问题，可以采取下面的步骤：（a）在唯一性约束的列上创建非唯一性索引（普通索引）；（b）添加唯一性约束；

 

4.3、外键列索引的规范

原则

无论表的大小，外键列都要建立索引

原因

其一是为了子父表关联查询的性能考虑，其二是为了避免父子表修改而发生死锁

实施

普通表的外键列建立普通索引即可，如果表是分区表，则依据表的情况建立本地索引或者全局索引

 

4.4、复合索引的规范

原则

复合索引只有在该种复合常被和该表相关的大多数SQL使用时才建立

衡量

复合索引的列数不能超过3个，否则该索引很少会被使用

实施

(1)复合索引的第一列，可以通过不使用该种复合的SQL来确定。假设一些SQL的WHERE中复合使用列为ABC，而其他一些SQL的WHERE中常使用的是C列，那么该复合索引可以按照CAB的顺序建立，这样上述两种SQL都能使用该索引；

(2)对于不能把握好的复合索引，请在选择性大的列上分别建立单列索引；

(3)切忌不能将表相关的所有SQL中WHERE涉及到的列复合起来建立复合索引；

 

4.5、函数索引的规范

原则

由于使用形式需和创建形式一致，尽量避免使用函数索引

衡量

如果想要使用函数索引，请尽量进行转化

方法

由于函数索引在使用时，使用形式必须和创建形式一致，故应该尽量避免使用函数索引，尽量采用如下方法转化SQL以避免函数索引的使用：

原本在WHERE中列上添加函数的，取函数的反意义函数添加到“=”另一侧的常数项上，这样只需要在列上建立普通索引即可，比如常见的日期转化函数：

TO_CHAR(CREATE_TIME)=’2010-07-07’采用TO_DATE()转化为CREATE_TIME=TO_DATE(‘2010-07-07’,’yyyy-mm-dd’)

4.6、位图索引的规范

原则

静态表中的低基数列可以使用位图索引

衡量

在事务型数据库（OLTP）中禁止使用位图（bitmap）索引，在报表型数据库（OLAP）中的静态表，可以适当使用；

方法

对于常发生DML操作的表，不能建立位图索引，请建立普通的索引即可，否则该表的相关操作很容易造成锁等待，使系统性能大受影响；其次，索引列需要低基数，只有几个数值，比如性别列（男，女，保密）和学历列（大专，本科，研究生，博士生）

 

4.7、反向索引的规范

原则

列值顺序增加的列，其上的WHERE运算是<>或者=而不是范围（between and或者 < and >）检索时，可以采用反向函数

衡量

一般创建反向索引的列为NUMBER类型，值由SEQUENCE生成

方法

 

实施

 

 

 

4.8、分区索引的规范

原则

对分区表的索引，需要做分区维护的，必须使用局部索引

衡量

一般情况下，HASH分区表可以采用全局索引，其他分区，包括RANGE-HASH也应该采用本地索引，主要是由于HASH分区表不常进行分区维护；

方法

 

实施

 

 

 

4.9、索引重建的规范

原则

重建索引使用ALTER INDEX REBUILD方式，禁止采用DROP INDEX & CREATE INDEX方式；

衡量

分区表等大数据量表的索引必须采用ALTER INDEX REBUILD方式重建

方法

ALTER INDEX  IDX_NAME REBUILD [TABLESPACE TBSP_NAME]

实施

可以在现网上直接实施

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27573546/viewspace-761476/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27573546/viewspace-761476/