分割槽索引學習筆記

續接上次的分割槽表學習筆記，對分割槽索引進行了總結。

--index maintance
SQL> select index_name,table_name from user_indexes where table_name='RANGE_PART';

no rows selected

--create one global index
SQL> create index glb_range_part on range_part(a,b)
  2  global partition by range(a)
  3  ( partition part_01 values less than(1000),
  4    partition part_02 values less than(maxvalue)
  5  );

Index created.
SQL> create index ind_range_part on range_part(a,b) local;
create index ind_range_part on range_part(a,b) local
                                          *
ERROR at line 1:
ORA-01408: such column list already indexed
--如果已經定義了index的列，則不能再建立其他的索引
--再次驗證
SQL> create index ind_range_part on range_part(a,b);

Index created.

SQL> create index ind_range_part on range_part(a,b) local;
create index ind_range_part on range_part(a,b) local
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

--提示是index名字重複了，似乎可以重新建一個其他名字的index
SQL> create index ind1_range_part on range_part(a,b);
create index ind1_range_part on range_part(a,b)
                                           *
ERROR at line 1:
ORA-01408: such column list already indexed
--這次還是回到上一步,錯誤重現

--如果不加local，索引在status列會有不同
SQL> select index_name,table_name,status from user_indexes where table_name='RANGE_PART';

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
IND_RANGE_PART                 RANGE_PART                     VALID

SQL> drop index ind_range_part;

Index dropped.

SQL> create index ind_range_part on range_part(a,b) local;

Index created.

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

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
IND_RANGE_PART                 RANGE_PART                     N/A

關於global index還有一點是global partition Index必須是prefixed的
SQL> create index ind_range_part on range_part(b,a)
global partition by range(a)
( partition part_01 values less than(1000),
  partition part_02 values less than(maxvalue)
)
SQL> /
global partition by range(a)
                           *
ERROR at line 2:
ORA-14038: GLOBAL partitioned index must be prefixed

--清除所有的索引，重新測試

SQL> create index ind_range_part on range_part(a,b)
global partition by range(a)
( partition glb_part_01 values less than(1000),
  partition glb_part_02 values less than(maxvalue)
)
  2    3    4    5    6 
SQL> /

Index created.

 

SQL> create index ind_range_part2 on range_part(b,a) local;

Index created.

 1* select index_name,status from user_indexes where table_name='RANGE_PART'
SQL> /

INDEX_NAME                     STATUS
------------------------------ --------
IND_RANGE_PART                 N/A
IND_RANGE_PART2                N/A

SQL> select index_name,partition_name,high_value,status from user_ind_partitions where index_name in(select index_name from user_indexes where table_name='RANGE_PART');

INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE           STATUS
------------------------------ ------------------------------ -------------------- --------
IND_RANGE_PART                 GLB_PART_01                    1000                 USABLE
IND_RANGE_PART2                PART_05                        MAXVALUE             USABLE
IND_RANGE_PART2                PART_02                        4000                 USABLE
IND_RANGE_PART2                PART_01                        2000                 USABLE
IND_RANGE_PART                 GLB_PART_02                    MAXVALUE             USABLE

SQL> alter table range_part merge partitions part_01,part_02;

Table altered.

SQL> select index_name,partition_name,high_value,status from user_ind_partitions where index_name in(select index_name from user_indexes where table_name='RANGE_PART');

INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE           STATUS
------------------------------ ------------------------------ -------------------- --------
IND_RANGE_PART                 GLB_PART_01                    1000                 UNUSABLE
IND_RANGE_PART2                PART_05                        MAXVALUE             USABLE
IND_RANGE_PART                 GLB_PART_02                    MAXVALUE             UNUSABLE
IND_RANGE_PART2                SYS_P51                        4000                 UNUSABLE

--分割槽編譯
SQL> alter index ind_range_part2 rebuild partition SYS_P51;

Index altered.
INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE           STATUS
------------------------------ ------------------------------ -------------------- --------
IND_RANGE_PART                 GLB_PART_01                    1000                 UNUSABLE
IND_RANGE_PART2                PART_05                        MAXVALUE             USABLE
IND_RANGE_PART                 GLB_PART_02                    MAXVALUE             UNUSABLE
IND_RANGE_PART2                SYS_P51                        4000                 USABLE

--對全域性索引全表rebuild失敗
SQL> ALTER INDEX IND_RANGE_PART REBUILD;
ALTER INDEX IND_RANGE_PART REBUILD
            *
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole
--只能根據分割槽來相應rebuild

SQL> ALTER INDEX IND_RANGE_PART REBUILD PARTITION GLB_PART_01;

Index altered.

SQL> ALTER INDEX IND_RANGE_PART REBUILD PARTITION GLB_PART_02;

Index altered.

SQL> ALTER TABLE RANGE_PART RENAME PARTITION SYS_P51 TO PART_01;

Table altered.

SQL> ALTER TABLE RANGE_PART SPLIT PARTITION PART_01 AT(2000) INTO (PARTITION PART_01,PARTITION PART_02);

Table altered.

SQL> select index_name,partition_name,high_value,status from user_ind_partitions where index_name in(select index_name from user_indexes where table_name='RANGE_PART');

INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE           STATUS
------------------------------ ------------------------------ -------------------- --------
IND_RANGE_PART                 GLB_PART_01                    1000                 UNUSABLE
IND_RANGE_PART2                PART_05                        MAXVALUE             USABLE
IND_RANGE_PART2                PART_02                        4000                 UNUSABLE
IND_RANGE_PART2                SYS_P51                        2000                 UNUSABLE
IND_RANGE_PART                 GLB_PART_02                    MAXVALUE             UNUSABLE

--PARTITION_NAME為SYS_P51，這個是Index的partition_name
SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='RANGE_PART';

PARTITION_NAME
------------------------------
PART_01
PART_02
PART_05

--分割槽名字沒有問題

--rebuild索引有以下的方式
SQL> ALTER TABLE RANGE_PART MODIFY PARTITION PART_01 REBUILD UNUSABLE LOCAL INDEXES;

Table altered.

SQL> ALTER INDEX IND_RANGE_PART2 REBUILD PARTITION PART_02;

Index altered.

最後有幾個檢視需要注意一下。

SQL> SELECT PARTITION_COUNT,STATUS,TABLE_NAME FROM USER_PART_TABLES WHERE TABLE_NAME='RANGE_PART';

PARTITION_COUNT STATUS   TABLE_NAME
--------------- -------- ------------------------------
              3 VALID    RANGE_PART

--查詢partition key
SQL> SELECT NAME,OBJECT_TYPE,COLUMN_NAME FROM USER_PART_KEY_COLUMNS WHERE NAME='RANGE_PART';

NAME                           OBJEC COLUMN_NAME
------------------------------ ----- --------------------
RANGE_PART                     TABLE A

--USER_TAB_PARTITIONS
--USER_IND_PARTITIONS