Oracle索引技术之如何建立最佳索引.docx

HYPERLINK"http://www.cnblogs.com/bdzwater/archive/2011/12/11/2283802.html"Oracle索引技术之如何建立最佳索引
怎样建立最佳索引?
1、明确地创建索引
createindexindex_nameontable_name(field_name)
tablespacetablespace_name
pctfree5
initrans2
maxtrans255
storage
(
minextents1
maxextents16382
pctincrease0
);

2、创建基于函数的索引
常用与UPPER、LOWER、TO_CHAR(date)等函数分类上，例：
createindexidx_funconemp(UPPER(ename))tablespacetablespace_name;

3、创建位图索引
对基数较小，且基数相对稳定的列建立索引时，首先应该考虑位图索引，例：
createbitmapindexidx_bitmonclass(classno)tablespacetablespace_name;

4、明确地创建唯一索引
可以用createuniqueindex语句来创建唯一索引，例：
createuniqueindexdept_unique_idxondept(dept_no)tablespaceidx_1;

5、创建与约束相关的索引
可以用usingindex字句，为与unique和primarykey约束相关的索引，例：
altertabletable_name
addconstraintPK_primary_keynameprimarykey(field_name)
usingindextablespacetablespace_name;

如何创建局部区索引？
1)基础表必须是分区表
2)分区数量与基础表相同
3)每个索引分区的子分区数量与相应的基础表分区相同
4)基础表的自分区中的行的索引项，被存储在该索引的相应的自分区中，例如
createindexTG_CDR04_SERV_ID_IDXonTG_CDR04(SERV_ID)
Pctfree5
TablespaceTBS_AK01_IDX
Storage(
MaxExtents32768
PctIncrease0
FreeLists1
FreeListGroups1
)
local
/

如何创建范围分区的全局索引？
基础表可以是全局表和分区表
createindexidx_start_dateontg_cdr01(start_date)
globalpartitionbyrange(start_date)
(partitionp01_idxvlaueslessthan('0106')
partitionp01_idxvlaueslessthan('0111')
...
partitionp01_idxvlaueslessthan('0401'))
/

如何重建现存的索引？
重建现存的索引的当前时刻不会影响查询
重建索引可以删除额外的数据块
提高索引查询效率
alterindexidx_namerebuildnologging;
对于分区索引
alterindexidx_namerebuildpartitionpartition_namenologging;

删除索引的原因？
1)不再需要的索引
2)索引没有针对其相关的表所发布的查询提供所期望的性能改善
3)应用没有用该索引来查询数据
4)该索引无效，必须在重建之前删除该索引
5)该索引已经变的太碎了，必须在重建之前删除该索引
语句：
dropindexidx_name;
dropindexidx_namepartitionpartition_name;

建立索引的代价？
基础表维护时，系统要同时维护索引，不合理的索引将严重影响系统资源，
主要表现在CPU和I/O上。
插入、更新、删除数据产生大量dbfilesequentialread锁等待。