全局分区索引与局部分区索引

分区索引 分区索引，有是全局分区索引与局部分区索引，加上一种全局非分区索引（也就是普通索引），加起来共三种。下面我们讨论了这三种索引的组织结构以及应用场景。 1.全局非分区索引可以依赖普通的表，也可以依赖分区表建立。 CREATE INDEX month_ix ON s

分区索引 分区索引，有是全局分区索引与局部分区索引，加上一种全局非分区索引（也就是普通索引），加起来共三种。下面我们讨论了这三种索引的组织结构以及应用场景。

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;