关于Oracle数据库表分区

参考：http://sishuok.com/forum/blogPost/list/6409.html#23117

1.    本地前缀索引

    本地分区索引是使用了LOCAL属性创建的分区索引，其特征是索引分区的所有键均指向其基表某个 唯一分区中存储的相应行。Oracle创建本地分区索引的目的就是要确保索引也是分区管理的，而且索引的分区与表的分区是均衡的，也就是本地分区索引具有与其基表相同的分区、子分区，即分区键等同于表的分区键、分区数等同于表的分区数。

任何基表分区的增加、删除、合并、分割操作，或者散列分区增加或合并操作，Oracle会通过其自身的机制自动维护本地分区索引相应的分区，此即本地分区索引与基表的均衡性原则。

如果分区列能够形成索引列的一个子集，则本地分区索引可以是唯一索引。该限制能确保具有相同索引键的行始终映射到同一个分区，在该分区中，违反唯一性的行为能被检测到。

本地索引的优势有：

l在基表上执行除SPLIT PARTITION或ADD PARTITION 外的维护命令仅仅只有一个分区会被影响

l当分区表只有一个本地分区索引时，对分区进行维护操作的时间是与分区的大小成正比的

l本地分区索引支持分区的独立性

l只能本地分区索引支持单一分区数据的装入和卸出

l本地索引与基表的均衡性会给Oracle执行计划带来更好的性能

l表分区和各自的本地索引可以同时恢复

l分区表中的位图索引必须是本地索引，非分区表上不能建立分区位图索引

  本地前缀索引是指以索引列的左前缀来分区的，如果存在子分区则要求其子分区的分区键包含在索引键中。本地前缀索引可以是唯一索引，也可以是非唯一索引。

2.本地非前缀索引

  本地非前缀索引是指没有以索引列的左前缀来分区的，或者是以索引列的左前缀来分区，但子分区的分区键不在索引键中。本地非前缀索引不可以是唯一索引，除非分区键是索引键的子集（此时是前缀索引）。

全局分区索引

   全局分区索引是指某个特定索引分区中的键可能指向存储在基表中的多个分区或子分区中的行，其创建需要使用GLOBAL属性。无论分区表是那种类型的分区，全局索引只支持按范围和散列分区两种分区方式。

 管理全局分区索引

l当基表分区移动和删除（TRUNCATE、DROP、MOVE、SPLIT）时，全局索引的所有分区都受影响，也不支持分区依赖

l当基表分区或子分区恢复到某个时间点时，全局索引中所有相应入口也要恢复到相同的时间点。由于索引的分区或子分区的入口可能离散分布，其它分区或子分区混合型入口不恢复，除了重建索引之外没有办法完成

分区索引的使用建议

lOLTP系统中，全局索引和本地前缀索引因能减少分区探测的次数从而提供更好的性能

lOLTP系统中，当有表分区或子分区维护操作的时候，本地索引提供更好有效性；非前缀分区索引对于历史数据库非常有用

lDSS系统中，本地非前缀索引能提高性能，原因是依据范围的并发查询（如BETWEEN）能够并发的扫描到到更多索引分区

l历史表的索引尽可能采用本地索引，因此历史表上会有较规律的分区删除操作，而采用本地索引能降低这类操作的影响

l多列上的唯一索引必须是全局的，因为本地唯一非前缀索引的键要求分区键必须是索引键的子集，而此时就不是非前缀索引了而是前缀索引了

除了注重以上信息之外，另关注以下： 

K: 没有建立本地前缀索引之前，查询已经可以达到分区裁剪了，如果建立本地前缀索引，查询范围比较大的，也未必适用本地前缀索引，查询范围很小的话，会使用本地前缀索引，而且性能比B-Tree性能好。

K: 对于分区表新增分区，不会影响全局索引；

K: 对于没有资料的新分区或者空分区，TRUNCATE/DROP/SPLIT不用影响全局索引；

K: SPLIT有数据的分区后，SPLIT涉及分区的本地索引需要重建；

K: 对于本地索引来说，本地非前缀索引可以建立唯一索引,只要包含表分区键就可以建立唯一索引；

K: 对于本地索引来说，不包含表分区键不可以建立唯一索引；

K: 对于分区索引来说，如果需要重建则需要一个一个分区重建；

K: 通常情况下，全局普通索引比本地非前缀索引要快一些！

K: 无法显式的删除本地索引的分区，删除的唯一方式是本地索引分区基表的分区被删除时由Oracle自动的隐式删除;

K: 如果全局索引分区是空的，则可以显式的删除它，使用的语句是ALTER INDEX index_name DROP PARTITION partition_name。但是，如果全局索引分区包含数据，删除则会引起更高级的分区（即下一个分区）变得不可用。如果非要这么做，则对更高级的分区需要重构;

K: 本地索引分区无法显式的拆分，其拆分的唯一途径是基表的分区拆分时由Oracle隐式的进行拆分;

K: 全局索引分区可以拆分，拆分完成后需要重构，但我们不推荐这么做。

K: 对于索引分区，切记不要通过MOVE指令来执行，虽然Oracle支持，建议一律先删除索引再重建;

K: ALTER TABLE xxx DROP PARTITION yyy UPDATE GLOBAL INDEXES 并不会影响DML操作;

参考：http://sishuok.com/forum/blogPost/list/6409.html#23117

Oracle局部索引和全局索引

局部索引local index

create index idx_ta_c2 on ta(c2) local
1. 局部索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区说，一句话，局部索引的分区机制和表的分区机制一样。 
2. 如果局部索引的索引列以分区键开头，则称为前缀局部索引。 
3. 如果局部索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀索引。 
4. 前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。 
5. 局部索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用局部索引去给表做唯一性约束，则约束中必须要包括分区键列。

如果要用局部索引去给表做唯一性约束，则约束中必须要包括分区键列
ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (SEQ_ID,PARTITION_ID) USING INDEX LOCAL;
6. 局部分区索引是对单个分区的，每个分区索引只指向一个表分区，全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区， 
对分区表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，局部分区索引具有更高的可用性。 
7. 位图索引只能为局部分区索引。 
8. 局部索引多应用于数据仓库环境中.

9. 分区索引就是在所有每个区上单独创建索引，它能自动维护，在drop或truncate某个分区时不影响该索引的其他分区索引的使用，也就是索引不会失效，维护起来比较方便，但是在查询性能稍微有点影响.

全局索引global index

create index idx_ta_c3 on ta(c3) global;

create index idx_ta_c3 on ta(c3) ;
1. 全局索引的分区键和分区数和表的分区键和分区数可能都不相同，表和全局索引的分区机制不一样。 
2. 全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前几列。 
3. 全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只动，截断一个分区中的数据，都需要rebulid若干个分区甚 
至是整个索引。 
4. 全局索引多应用于oltp系统中。 
5. 全局分区索引只按范围或者散列hash分区，hash分区是10g以后才支持。 
6. oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。

alter table table_name drop partition partition_name update global indexes
7. 表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引.