分区索引
分区索引，有是全局分区索引与局部分区索引，加上一种全局非分区索引（也就是普通索引），加起来共三种。下面我们讨论了这三种索引的组织结构以及应用场景。
1.全局非分区索引
可以依赖普通的表，也可以依赖分区表建立。
CREATE INDEX month_ix ON sales(sales_month);
等同于CREATE INDEX month_ix ON sales(sales_month) GLOBAL;
2.全局分区索引
全局分区索引使用一种有别于底层表的分区机制，意思是索引的分区键可以选择跟表的分区键不一致，但索引的索引键前缀要包含索引的分区键。也就是只有”全局前缀索引“，而没有“全局非前缀索引”。这样，拿了索引分区键做前缀的索引，即使不包含表分区键，也能用于表的unique与primary约束。
建成后有多个段，每个段代表一个索引分区，每个索引分区中的键值可以指向任何表分区。可以依赖普通的表，也可以依赖分区表建立。可能索引分区数不等于表分区数。只能按range或hash(10g起)对索引分区。全局索引的range分区最后一个分区必须是maxvalue，以保证底层表的所有行都能放到这个索引中。
CREATE INDEX month_ix ON sales(sales_month,sales_date) GLOBAL PARTITION BY RANGE(sales_month)
(PARTITION pm1_ix VALUES LESS THAN (2),
PARTITION pm2_ix VALUES LESS THAN (3),
PARTITION pm3_ix VALUES LESS THAN (4),
PARTITION pm4_ix VALUES LESS THAN (5),
PARTITION pm12_ix VALUES LESS THAN (MAXVALUE));
全局索引建立时global 子句允许指定索引的范围值，这个范围值是索引分区键的范围。全局分区索引的GLOBAL PARTITION BY RANGE(sales_month)的sales_month是指定索引分区键，可以跟表分区键不一样，我行我素地设立分区键，此时sales(sales_month,sales_date)句子，指定索引键，其前缀就必须包含索引分区键了。这一切，都可以跟底层表没啥关系。
使用场景：
对于数据仓库，例如不断有旧数据的删除与新数据的流入（滑动窗口），全局索引很容易失效，使性能受影响。
3.局部分区索引
不能对普通表建这个索引，只能依赖分区表建立，并且是依赖分区表的分区键来建立，即依赖底层表的分区机制来建立索引。随着表分区，建立一一对应的索引分区，每个索引分区中的条目都只指向一个表分区。
CREATE INDEX loc_dept_ix ON dept(deptno) LOCAL;
create index dinya_idx_t on dinya_test(item_id)
local
(
partition idx_1 tablespace tbs1,
partition idx_2 tablespace tbs2,
partition idx_3 tablespace tbs3
);
局部分区索引逻辑上可以划分为：
局部前缀索引--表分区键在索引定义的第一列上。例如对表的字段LOAD_DATE进行range分区，而建索引时，LOAD_DATE又是索引的第一列。
局部非前缀索引--索引不以表分区键作为它的索引字段的第一列，甚至压根不包含分区键。
局部前缀索引与局部非前缀索引，对分区消除的影响？
首先我们得明白什么是分区消除。一个事务，可以只考虑特定的分区，其余分区就算物理介质损坏，其他分区所在表空间offline等，事务都可以不理会以及不扫描他们。分区消除的种类：表的分区消除，与索引的分区消除。分区消除更多的是为了可用性，以及在出现全表扫与全索引扫的时候，转换为只扫特定的分区以提高性能。
能否使用分区消除，关键在于谓词是否有分区键。如果谓词包含分区键，那可能是实现索引分区消除，也可能是表分区消除。如果谓词不包含分区键，那神马分区消除都是奢想。至于使用的是局部前缀索引还是局部非前缀索引，影响的只是能否实现索引分区消除。用局部前缀索引才能实现索引分区消除，用局部非前缀索引，不能实现“索引分区消除“（但表的分区消除仍然可能实现,但当cbo评估出来要先走索引，却发现索引分区不可用，如所在表空间offline了，此时已不能改路了）。cbo评估代价时，不会考虑分区索引是否可用，评估出一个路径，走下去发现此路不通，也不能走回头路了而直接报错了。
局部前缀索引与局部非前缀索引，对于sql执行性能？
如果将索引作为查询计划的第一步，效率上其实并没有什么区别，尽管前缀与非前缀索引会影响到是否能使用分区消除，但分区消除是什么呢？是可用性的提高，以及将全表扫描转为单分区全扫的性能上的优化。所以对于走索引作为第一步，是否分区消除不要紧，从而是否前缀也就不要紧了。
局部前缀索引与局部非前缀索引的选择？
怎么选择，首先应该是能满足需求的。你如果建立一个(b,a)的索引，却总查where a=3，引出很多skip scan那就不好了，此时是应该换成建立(a,b)的索引。
如果仅仅有where a=1 and b=2这样的查询，你可能会问，我们是建(a,b)好还是(b,a)好呢，看哪个字段的选择性好，看我们有没有必要走a的索引分区消除，假如b的密度很大，从1-50000都有，而a只能是1与2，那么我们把b排前面更好。所以将哪个字段放前面，得满足业务需求、综合谓词的分区消除，与字段选择率来选择。
局部索引与唯一约束
分区表字段想用unique或primary key约束，一般是使用全局索引来保证唯一性，这是一般的做法。因为局部索引只保证分区内部的键的唯一性，而不能跨分区，如果你的确想用局部索引来保证整个表的唯一性，就得把分区键加到约束当中，也成。如果oracle允许局部索引（不包含约束的情况）就能轻易来保证全表的唯一性，那么所有的update与insert，都得扫每一个分区，这样可用性与可扩展性都会丧失殆尽。
4.总结
三种索引的选择？
OLAP系统中多用局部索引，OLTP系统上，全局索引更为常见。
可用性角度：局部索引更可用，就算一个索引分区出问题了也不影响其他，而全局索引很可能会成为一个故障点，一旦出现问题则整个索引都不可用。
维护性角度：局部索引更好维护更灵活，DBA决定移动一个表分区，只需要重建与维护一个索引分区。对全局索引，很多情况下都需重建。
sql效率：因为局部索引随表分区，可以涉及出最优的执行计划。
视图
select * from DBA_IND_PARTITIONS where index_name=‘LOCAL_NOPREFIXED‘;
select * from DBA_PART_INDEXES where index_name=‘LOCAL_NOPREFIXED‘;
select * from DBA_PART_KEY_COLUMNS where name=‘LOCAL_NOPREFIXED‘;
实验：
--创建表空间tbs1,tbs2,tbs3
create tablespace tbs1 datafile ‘+DATA6_MIDG‘;
create tablespace tbs2 datafile ‘+DATA6_MIDG‘;
create tablespace tbs3 datafile ‘+DATA6_MIDG‘;
SQL>
--创建一个range分区表
create table t_part(a int,b int,data char(20))
partition by range (a)
(partition p1 values less than(2) tablespace tbs1,
partition p2 values less than(3) tablespace tbs2
);
--插入一些数据
insert into t_part select mod(rownum-1,2)+1,rownum,‘x‘ from all_objects;
commit;
SQL> select * from t_part where rownum<=10;
--创建局部前缀索引与局部非前缀索引
create index local_prefixed on t_part(a,b) local;
create index local_noprefixed on t_part(b) local;
create index local_prefixed on t_part(a,b) local
(partition ind1 tablespace tbs3,
partition ind2 tablespace tbs2);
--为表收集统计信息
begin dbms_stats.gather_table_stats(user,‘t_part‘,cascade=>TRUE);end;
--把tbs2表空间下线，此时可以验证索引分区消除，将tbs1下线，可以验证表分区消除。
alter tablespace tbs3 offline;
--再用这种命令来测试
select * from t_part where a=1 and b=1;
select * from t_part where b=1;
select /*+full(t_part)*/ * from t_part where a=1 and b=1;