热门标签 | HotTags
当前位置:  开发笔记 > 数据库 > 正文

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

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

分区索引 分区索引,有是全局分区索引与局部分区索引,加上一种全局非分区索引(也就是普通索引),加起来共三种。下面我们讨论了这三种索引的组织结构以及应用场景。 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 &#39;+DATA6_MIDG&#39;; create tablespace tbs2 datafile &#39;+DATA6_MIDG&#39;; create tablespace tbs3 datafile &#39;+DATA6_MIDG&#39;; 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,&#39;x&#39; 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,&#39;t_part&#39;,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;


推荐阅读
author-avatar
空荡荡跑龙套
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有