partition_global index补疑(一)

----创建分区表
SQL> create table t_partition(a int,b int)
  2  partition by range(a)
  3  (partition p1 values less than(10),
  4   partition p2 values less than(20),
  5   partition p3 values less than(maxvalue)
  6  )
  7  /

Table created.

--在分区表创建普通唯一索引
SQL> create unique index idx_t_partition on t_partition(a);

Index created.

-普通唯一索引未分区
SQL> select index_name,partition_name from user_ind_partitions where index_name=
(select index_name from user_indexes where table_name='T_PARTITIOIN');

no rows selected

SQL> drop index idx_t_partition;

Index dropped.

SQL> create index idx_t_partition on t_partition(a) local;

Index created.

SQL> select index_name,partition_name from user_ind_partitions where index_name
(select index_name from user_indexes where table_name='T_PARTITIOIN');

no rows selected

SQL> select index_name,partition_name from user_ind_partitions where index_name
(select index_name from user_indexes where table_name='T_PARTITION');

INDEX_NAME
------------------------------------------------------------
PARTITION_NAME
------------------------------------------------------------
IDX_T_PARTITION
P2

IDX_T_PARTITION
P1

IDX_T_PARTITION
P3

---创建全局索引
SQL> create unique index idx_t_partition on t_partition(a) global;

Index created.

---创建的全局索引未分区
SQL> select index_name,partition_name from user_ind_partitions where index_name=
(select index_name from user_indexes where table_name='T_PARTITION');

no rows selected

SQL>

SQL> select index_name,status from user_indexes where table_name='T_PARTITION';

INDEX_NAME                                                   STATUS
------------------------------------------------------------ ----------------
IDX_T_PARTITION                                              VALID

SQL> select * from t_partition partition(p1);

         A          B
---------- ----------
         2          1

SQL> alter table t_partition truncate partition p1;

Table truncated.

---全局索引维护分区会失效
SQL> select index_name,status from user_indexes where table_name='T_PARTITION';

INDEX_NAME                                                   STATUS
------------------------------------------------------------ ----------------
IDX_T_PARTITION                                              UNUSABLE

SQL> create index idx_t_partition on t_partition(a) local;

Index created.

SQL> desc t_partition;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------

 A                                                  NUMBER(38)
 B                                                  NUMBER(38)

SQL> alter table t_partition modify b not null;

Table altered.

SQL> create index idx_pk_global on t_partition(b)                            
  2  global                                                                  
  3  partition by range(b)                                                   
  4  (partition p1 values less than (10),                                    
  5   partition p2 values less than (20),                                    
  6   partition p3 values less than (maxvalue)                               
  7  )                                                                       
  8  /                                                                       
                                                                             
Index created.                                                               

---全局索引以分区键进行分区                                                                             
SQL> select index_name,partition_name from user_ind_partitions where index_nam
'IDX_PK_GLOBAL';                                                             
                                                                             
INDEX_NAME                                                                   
------------------------------------------------------------                 
PARTITION_NAME                                                               
------------------------------------------------------------                 
IDX_PK_GLOBAL                                                                
P1                                                                           
                                                                             
IDX_PK_GLOBAL                                                                
P2                                                                           
                                                                           
IDX_PK_GLOBAL                                                              
P3                                                                         
                                                                           

---全局索引也可仅指定global创建
SQL> create index idx_pk_global on t_partition(b) global;

Index created.

SQL>

---不指定global后的分区,全局索引不分区
SQL> select index_name,partition_name from user_ind_partitions where index_name=
'IDX_PK_GLOBAL'
  2  /

no rows selected

SQL> select index_name from user_indexes where index_name='IDX_PK_GLOBAL';

INDEX_NAME
------------------------------------------------------------
IDX_PK_GLOBAL

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-752361/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-752361/