Oracle分区之五:创建分区索引总结 | 您所在的位置:网站首页 › 分区表建索引要注意什么 › Oracle分区之五:创建分区索引总结 |
分区索引总结:
一,分区索引分为2类:
1、global,它必定是Prefix的。不存在non-prefix的 2、local,它又分成2类: 2.1、prefix:索引的第一个列等于表的分区列。 2.2、non-prefix:索引的第一个列不等于表的分区列。 LOCAL的索引只能是表的分区方式,不能自己写分区方式。他们是EQUI-Partition的。GLOBAL索引可以不分区,这个时候就是普通的一个索引。同一个列只能只有一个索引,这个列可以是GLOBAL或者是LOCAL的索引。如果唯一索引所在的列不是表的分区列,只能建立GLOBAL索引。 例如:分区表 create table test (id number,data varchar2(100)) partition by RANGE (id) ( partition p1 values less than (10000) , partition p2 values less than (20000) , partition p3 values less than (maxvalue) ); 在ID列上创建一个LOCAL的索引 create index id_local on test(id) local; SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_LOCAL'; INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS ------------------------------ ------------------------------ -------------------- -------- ID_LOCAL P1 10000 USABLE ID_LOCAL P2 20000 USABLE ID_LOCAL P3 MAXVALUE USABLE 从上面可以看出索引的分区和表一样,即是EQUI-PARTITION 如果我在表上增加个分区,则ORACLE会自动维护分区的索引,注意此时加分区必须是用split,直接加会出错的。例如: SQL> alter table test add partition p4 values less than (30000); alter table test add partition p4 values less than (30000) * ERROR at line 1: ORA-14074: partition bound must collate higher than that of the last partition SQL> alter table test split partition p3 at (30000) into (partition p3, partition p4); Table altered. SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_LOCAL'; INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS ------------------------------ ------------------------------ -------------------- -------- ID_LOCAL P1 10000 USABLE ID_LOCAL P2 20000 USABLE ID_LOCAL P3 30000 USABLE ID_LOCAL P4 MAXVALUE USABLE
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME from dba_indexes where index_name='ID_LOCAL'; INDEX_NAME INDEX_TYPE TABLE_NAME ------------------------------ --------------------------- ------------------------------ ID_LOCAL NORMAL TEST 删除id_local索引 drop index id_local; 重新在ID列上创建一个GLOBAL的索引 create index id_global on test(id) global; SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_GLOBAL'; no rows selected SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME from dba_indexes where index_name='ID_GLOBAL'; INDEX_NAME INDEX_TYPE TABLE_NAME ------------------------------ --------------------------- ------------------------------ ID_GLOBAL NORMAL TEST 从上面可以看出,它此时是个普通索引。dba_ind_partitions里根本就没有记录。 SQL>create index i_id_global on test(data) global partition by range(id) ( partition p1 values less than (10000) , partition p2 values less than (MAXVALUE) ); partition by range(id) * ERROR at line 2: ORA-14038: GLOBAL partitioned index must be prefixed 此错误表示GLOBAL的索引必须是prefixed,即索引分区的列,必须是其基表的分区列。 SQL>create index id_global on test(id) global partition by range(id) ( partition p1 values less than (10000) , partition p2 values less than (MAXVALUE) ); Index created. SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_GLOBAL'; INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS ------------------------------ ------------------------------ -------------------- -------- ID_GLOBAL P1 10000 USABLE ID_GLOBAL P2 MAXVALUE USABLE SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME from dba_indexes where index_name='ID_GLOBAL'; INDEX_NAME INDEX_TYPE TABLE_NAME ------------------------------ --------------------------- ------------------------------ ID_GLOBAL NORMAL TEST 从上面可以看出,它此时是个GLOBAL的索引了。dba_ind_partitions里有记录。请和上面的做个比较,加深印象。 二,到底如何判断建立怎样的分区索引(GLOBAL 还是LOCAL) 我将用下面的例子来分析到底需要创建什么类型索引好。 create table TT(id number,createdate date) partition by range(createdate) ( partition Q1 VALUES LESS THAN (TO_DATE('2012-03-30','YYYY-MM-DD')), partition Q2 VALUES LESS THAN (TO_DATE('2012-06-30','YYYY-MM-DD')), partition Q3 VALUES LESS THAN (TO_DATE('2012-09-30','YYYY-MM-DD')), partition Q4 VALUES LESS THAN (TO_DATE('2012-12-31','YYYY-MM-DD')), partition Q_OTHERS VALUES LESS THAN (MAXVALUE) ); 注意:只能是to_date,其他的任何函数都不行,maxvalue必须在最后,他可以包括NULL值。 第一种情况:如果查询的语句的条件是where createdate='2012-10-19' and id>100,则此时查询的是4号分区,假设他有10万条记录。在扫描这10万条记录的时候, 可以使用id列上的索引。这个时候可以在ID列上建立个local nonprofiex索引 create index index_tt1_local on TT(id) local ( partition p1, partition p2, partition p3, partition p4, partition p5 ); 注意:索引分区的数量和其基本的分区数量要一样。 SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='INDEX_TT1_LOCAL'; INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS ------------------------------ ------------------------------ -------------------- -------- INDEX_TT1_LOCAL P1 TO_DATE(' 2012-03-30 USABLE 00:00:00', 'SYYYY-M M-DD HH24:MI:SS', 'N LS_CALENDAR=GREGORIA INDEX_TT1_LOCAL P2 TO_DATE(' 2012-06-30 USABLE 00:00:00', 'SYYYY-M M-DD HH24:MI:SS', 'N LS_CALENDAR=GREGORIA INDEX_TT1_LOCAL P3 TO_DATE(' 2012-09-30 USABLE INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS ------------------------------ ------------------------------ -------------------- -------- 00:00:00', 'SYYYY-M M-DD HH24:MI:SS', 'N LS_CALENDAR=GREGORIA INDEX_TT1_LOCAL P4 TO_DATE(' 2012-12-31 USABLE 00:00:00', 'SYYYY-M M-DD HH24:MI:SS', 'N LS_CALENDAR=GREGORIA INDEX_TT1_LOCAL P5 MAXVALUE USABLE
第二种情况: 如果查询的语句条件只有一个createdate,如where createdate='2010-10-19',则这种情况就在createdate上建立一个local profiex索引 SQL> create index index_TT2_local on TT(createdate) local; Index created. SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='INDEX_TT2_LOCAL'; INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS ------------------------------ ------------------------------ -------------------- -------- INDEX_TT2_LOCAL Q1 TO_DATE(' 2012-03-30 USABLE 00:00:00', 'SYYYY-M M-DD HH24:MI:SS', 'N LS_CALENDAR=GREGORIA INDEX_TT2_LOCAL Q2 TO_DATE(' 2012-06-30 USABLE 00:00:00', 'SYYYY-M M-DD HH24:MI:SS', 'N LS_CALENDAR=GREGORIA INDEX_TT2_LOCAL Q3 TO_DATE(' 2012-09-30 USABLE INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS ------------------------------ ------------------------------ -------------------- -------- 00:00:00', 'SYYYY-M M-DD HH24:MI:SS', 'N LS_CALENDAR=GREGORIA INDEX_TT2_LOCAL Q4 TO_DATE(' 2012-12-31 USABLE 00:00:00', 'SYYYY-M M-DD HH24:MI:SS', 'N LS_CALENDAR=GREGORIA INDEX_TT2_LOCAL Q_OTHERS MAXVALUE USABLE 从上面查询可以看出他和表是equi-partitioned. 第三种情况: 如果查询根本就没有createdate,而是有像where id>100的条件,则就只能在ID列上建立GLOBAL索引了 create index index_tt3_global on TT(id) global partition by range(id) ( partition p1 values less than (100000), partition p2 values less than (200000), partition p3 values less than (MAXVALUE) ); 从上面可以看出,GLOBAL的索引的分区数和其基本是没有关系的。他甚至可以想下面一个建立索引,即一个普通索引。但是LOCAL的必须和其基本分区数一致。 SQL> create index index_tt3_global on TT(id) global; Index created. 总之,一般建议建立LOCAL的索引,因为GLOBAL的容易所有的都失效,而LOCAL的最多只在某个分区上失效。索引失效必须一个分区的一个分区的REBUILD。 |
CopyRight 2018-2019 实验室设备网 版权所有 |