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