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的索引
SQL>create index id_local on test(id) local;

Index created.

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

--如果我在表上增加個分割槽，則會自動維護分割槽的索引,注意此時加分割槽必須是用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索引
SQL> drop index id_local;

Index dropped.

--重新在ID列上建立一個GLOBAL的索引
SQL> create index id_global on test(id) global;

Index created.

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> drop index id_global;

Index dropped.

注意：不刪會報：ORA-01408: such column list already indexed

--建立全域性索引

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索引了
SQL> drop index index_tt1_local;

Index dropped.

注意：不刪報ORA-01408: such column list already indexed

SQL> 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的必須和其基本分割槽數一致。

-建立需先刪索引index_tt3_global
SQL> create index index_tt3_global on TT(id) global;

Index created.

總之，一般建議建立LOCAL的索引，因為GLOBAL的容易所有的都失效，而LOCAL的最多隻在某個分割槽上失效。索引失效必須一個分割槽的一個分割槽的REBUILD。