ORACLE 分区索引

个人理解。

看了好几天，终于差不多弄明白了。

分区表不能在现有表上面改，必须重建。可以RENAME OLD_TABLE，然后CREATE相同表名的分区表，COPY数据。

下面就开始建索引，这一步有点难度。而且必须谨慎。

对分区表可以分为四种索引，全局不分区、全局分区、局部有前缀、局部无前缀。

决定使用哪种索引，要先分析所有相关的SQL，找出使用最频繁的那几条查询语句。针对这些SQL，开始设计索引。

全局不分区索引：CREATE INDEX IND1 ON TB_NAME(COL_A) GLOBAL;这种跟最一般的索引可能分别不大或者就是一样。不提。

全局分区索引：

TABLE表有四个列COL_A COL_B   COL_C   COL_D，COL_A为分区列。

如果SQL如FROM TABLE WHERE COL_B=#B;使用很频繁，这句查询语句是进入不到分区的，那就考虑在COL_B上建全局分区索引。相对于全部不分区索引效率高。

局部有前缀索引：

如果SQL如FROM TABLE WHERE COL_A=#A AND COL_B=#B AND COL_C=#C;使用非常频繁，可以考虑建局部有前缀索引，CREATE INDEX IND1 ON TABLE(COL_A,COL_B,COL_C) LOCAL;

这是复合索引，参考http://blog.chinaunix.net/uid-25557346-id-3243552.html

局部无前缀索引：

如果SQL如FROM TABLE WHERE COL_A=#A AND COL_B=#B;和FROM TABLE WHERE COL_A=#A AND COL_C=#C和FROM TABLE WHERE COL_A=#A AND COL_D=#D三种全部都使用频繁，可以考虑在三个列上建无前缀索引，ON TABLE(COL_B) LOCAL;   ON TABLE(COL_C) LOCAL;   ON TABLE(COL_D) LOCAL;

当然如果改成三个有前缀索引，如ON TABLE(COL_A,COL_B) LOCAL;查询效率会更高。但是索引维护开销可能很大。

关于全局分区索引，应该叫建索引分区，跟表分区类似，指定一个分区列，跟那个表分区的分区列是两码事，然后指定列值的范围，把这个列的全部索引树分成几部分，也就是索引分区。下次查询的时候，就不用遍历整个索引树了。

------------------------------------------------------------------------------------------

LOCAL只有在分区表可以用。否则会报错的。ORA-14...GLOBAL都可以用。
关于LOCAL本地索引：
如上，如果分区列为COL_A，查询条件包括COL_A时可以进入分区，否则仍然会全表扫描。
如果在COL_B建一个LOCAL无前缀索引，查询条件如WHERE T.COL_B=...不包含COL_A时仍然全表扫描。
    如果WHERE T.COL_A=#A AND COL_B=#B，会先进入分区，在访问COL_B索引，这种情况
    如果索引改为前缀索引CREATE INDEX INDEX_NAME ON TABLE_NAME(COL_A,COL_B) LOCAL;效率会更高。
    有前缀和无前缀体现在这里。

如果建立本地索引的时候，要建立的字段已经建立的全局索引，那建立这个索引的时候也要报错，反之亦然：（全局分区不分区索引都会）
全局分区索引可以建在分区列和其他列上，没有关系。
全局分区索引必须是前缀索引，指的是以全局分区索引使用的分区列为前缀，而不是TABLE表的分区列。
可以建全局不分区索引。对此没有前缀非前缀之分。

只有联合索引才有前缀无前缀的区别。
用分区列作联合索引的第一项就是前缀索引，否则是非前缀索引。
前缀与非前缀的区别不在于效率,
而在于查询条件中选择性最好的字段是否是索引的前导列.
全局索引也为全局非分区索引与全局分区索引,
全局非分区索引在进行跨分区扫描时很有优势,
本地索引在单个分区内扫描时很有优势.

参考

http://blog.chinaunix.net/uid-25557346-id-3243552.html
http://www.examw.com/oracle/jishu/187007/
http://www.itpub.net/thread-429282-3-1.html
http://blog.csdn.net/robinson1988/article/details/5276924
http://zhidao.baidu.com/question/424294866.html