Oracle创建分区索引总结

全局索引
============
CREATE INDEX month_ix ON sales(sales_month)

本地索引
============
CREATE INDEX loc_dept_ix ON dept(deptno) LOCAL;

全局分区索引
============
CREATE INDEX month_ix ON sales(sales_month)
   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 pm5_ix VALUES LESS THAN (6)
       PARTITION pm6_ix VALUES LESS THAN (7)
       PARTITION pm7_ix VALUES LESS THAN (8)
       PARTITION pm8_ix VALUES LESS THAN (9)
       PARTITION pm9_ix VALUES LESS THAN (10)
       PARTITION pm10_ix VALUES LESS THAN (11)
       PARTITION pm11_ix VALUES LESS THAN (12)
       PARTITION pm12_ix VALUES LESS THAN (MAXVALUE));

分区表和一般表一样可以建立索引，分区表可以创建局部索引和全局索引。当分区中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用全局索引。
3.1     局部索引分区的建立：
SQL> create index dinya_idx_t on dinya_test(item_id)
  2  local
  3  (
  4     partition idx_1 tablespace dinya_space01,
  5     partition idx_2 tablespace dinya_space02,
  6     partition idx_3 tablespace dinya_space03
  7  );

Index created.


3.2     全局索引分区的建立。
全局索引建立时global 子句允许指定索引的范围值，这个范围值为索引字段的范围值：
SQL> create index dinya_idx_t on dinya_test(item_id)
  2  global partition by range(item_id)
  3  (
  4     partition idx_1 values less than (1000) tablespace dinya_space01,
  5     partition idx_2 values less than (10000) tablespace dinya_space02,
  6     partition idx_3 values less than (maxvalue) tablespace dinya_space03
  7  );

Index created.

        本例中对表的item_id字段建立索引分区，当然也可以不指定索引分区名直接对整个表建立索引，如：
SQL> create index dinya_idx_t on dinya_test(item_id);

Index created.

一，分区索引分为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里有记录。请和上面的做个比较，加深印象。