Oracle分区之五：创建分区索引总结

Oracle分区之五：创建分区索引总结


http://blog.csdn.net/waterxcfg304/article/details/8518577
分区索引总结：
一，分区索引分为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。