12C新特性之表分割槽非同步全域性索引非同步維護(add、truncate、drop、spilt、merge多分割槽)

張衝andy發表於2017-05-13
實驗準備:
-- 建立實驗表
CREATE TABLE p_andy
(ID number(10), NAME varchar2(40))
PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (20)
);
Table created.
-- 檢視現在表的分割槽:
SQL> 
col table_name for a25
col partition_name for a25
select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE from user_tab_partitions where table_name='P_ANDY';

TABLE_NAME                PARTITION_NAME            PARTITION_POSITION TABLESPACE_NAME                HIGH_VALUE
------------------------- ------------------------- ------------------ ------------------------------ --------------
P_ANDY                    P1                                         1 USERS                          10
P_ANDY                    P2                                         2 USERS                          20
一、  一次add多個分割槽

SQL> 

ALTER TABLE P_ANDY ADD   
PARTITION p3 VALUES  LESS THAN (30),
PARTITION p4 VALUES LESS THAN (40);
Table altered.
-- 檢視add後的分割槽情況
SQL> 
select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE from user_tab_partitions where table_name='P_ANDY';
TABLE_NAME                PARTITION_NAME            PARTITION_POSITION TABLESPACE_NAME                HIGH_VALUE
------------------------- ------------------------- ------------------ ------------------------------ -----------
P_ANDY                    P1                                         1 USERS                          10
P_ANDY                    P2                                         2 USERS                          20
P_ANDY                    P3                                         3 USERS                          30
P_ANDY                    P4                                         4 USERS                          40
-- 插入資料
SQL> 
begin
for i in 1 .. 39 loop
insert into p_andy values(i,'andyi');
end loop ;
commit;
end;
/
PL/SQL procedure successfully completed.

二、  一次 truncate 或者 drop 多個分割槽 

TRUNCATE 和 EXCHANGE 分割槽及子分割槽。無論是 TRUNCATE 還是 EXCHANGE 分割槽,在主表上執行,都可以級聯的作用在子表、孫子表、重孫子表、重重重...孫子表上同時執行。對於 TRUNCATE 而言,所有表的 TRUNCATE 操作在同一個事務中,如果中途失敗,會回滾到命令執行之前的狀頭。這兩個功能透過關鍵字 CASCADE實現。

(說明這裡只演示truncate,drop使用只需要關鍵字替換就可以了)
方式一:truncate 不帶 update index ,如果表有全域性索引,則truncate partition會失全域性索引失效。
-- 建立一個全域性非分割槽索引
SQL> create index idx_pandy_id on p_andy(id);
Index created.
--檢視索引名字
SQL> 
col column_name for a40
SELECT index_name, column_name, descend  FROM user_ind_columns  WHERE table_name = 'P_ANDY';
INDEX_NAME                COLUMN_NAME                              DESC
------------------------- ---------------------------------------- ----
IDX_PANDY_ID              ID                                       ASC
-- 檢視索引狀態
SQL> 
col index_name for a25
select table_name,index_name,status,blevel,leaf_blocks from user_Indexes where index_name ='IDX_PANDY_ID';
TABLE_NAME                INDEX_NAME                STATUS       BLEVEL LEAF_BLOCKS
------------------------- ------------------------- -------- ---------- -----------
P_ANDY                    IDX_PANDY_ID              VALID             0           1
-- truncate多個分割槽 , without update index
SQL> ALTER TABLE p_andy TRUNCATE partition p3,p4;
Table truncated.
-- 檢視索引狀態
SQL> select table_name,index_name,status,blevel,leaf_blocks from user_Indexes where index_name ='IDX_PANDY_ID';
TABLE_NAME                INDEX_NAME                STATUS       BLEVEL LEAF_BLOCKS
------------------------- ------------------------- -------- ---------- -----------
P_ANDY                    IDX_PANDY_ID              UNUSABLE          0           1
方式二:truncate 帶 update index ,如果表有全域性索引,則truncate partition不會使全域性索引失效。
-- 插入資料
SQL>
begin
for i in 20 .. 39 loop
insert into p_andy values(i,'andyi');
end loop ;
commit;
end;
/
PL/SQL procedure successfully completed.
-- 檢視索引狀態
SQL> select table_name,index_name,status,blevel,leaf_blocks from user_Indexes where index_name ='IDX_PANDY_ID';
TABLE_NAME                INDEX_NAME                STATUS       BLEVEL LEAF_BLOCKS
------------------------- ------------------------- -------- ---------- -----------
P_ANDY                    IDX_PANDY_ID              UNUSABLE          0           1
-- 重建索引
SQL> alter index IDX_PANDY_ID rebuild;
Index altered.
-- 檢視索引狀態
SQL> select table_name,index_name,status,blevel,leaf_blocks from user_Indexes where index_name ='IDX_PANDY_ID';
TABLE_NAME                INDEX_NAME                STATUS       BLEVEL LEAF_BLOCKS
------------------------- ------------------------- -------- ---------- -----------
P_ANDY                    IDX_PANDY_ID              VALID             0           1
-- truncate多個分割槽,帶 update index
SQL> ALTER TABLE p_andy TRUNCATE partition p3,p4 UPDATE GLOBAL INDEXES;
Table truncated.
說明:
1、Oracle 12c 可以實現了非同步全域性索引非同步維護的功能,在分割槽維護操作,比如 DROP 或 TRUNCATE 後,仍然是 VALID 狀態,索引
不會失效,不過索引的狀態是包含 OBSOLETE 資料,當維護操作完成,索引狀態恢復。
2、12c 中資料字典DBA/ALL/USER_INDEXES OR DBA/ALL/USER_IND_PARTITIONS增加了列ORPHANED_ENTRIES ,表示當前全域性索引是否保含過期條目(索引有記錄,而表中的實際資料已經drop或者truncate)。
3、列ORPHANED_ENTRIES三個值
該列可能存在3個值:
? YES: 該索引存在orphaned(過期遊離)條目
? NO: 該索引不存在orphaned(過期遊離)條目
? N/A: 不適用的型別如非分割槽表索引或local 索引
4、 對於索引存在orphaned , 我們可以手動清除(方法推薦兩種)
法一:ALTER INDEX xxx  REBUILD;
法二:SQL> alter session force parallel ddl parallel 8; 手動維護時使用並行,加速維護 
exec DBMS_PART.CLEANUP_GIDX('[SCHEMA]','[TABLE NAME]');
-- 檢視索引狀態   ORPHANED_ENTRIES
SQL> select table_name,index_name,status,blevel,leaf_blocks,orphaned_entries from user_Indexes where index_name ='IDX_PANDY_ID';
TABLE_NAME                INDEX_NAME                STATUS       BLEVEL LEAF_BLOCKS ORP
------------------------- ------------------------- -------- ---------- ----------- ---
P_ANDY                    IDX_PANDY_ID              VALID             0           1 YES
三、  一次spilt多個分割槽
12c 中新增強的 SPLIT PARTITION 語句可以讓你只使用一個單獨命令將一個特定分割槽或子分割槽分割為多個新分割槽。
-- 插入資料
SQL>
begin
for i in 20 .. 39 loop
insert into p_andy values(i,'andyi');
end loop ;
commit;
end;
/
-- 檢視分割槽情況
SQL> 
select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE from user_tab_partitions where table_name='P_ANDY';
TABLE_NAME                PARTITION_NAME            PARTITION_POSITION TABLESPACE_NAME                HIGH_VALUE
------------------------- ------------------------- ------------------ ------------------------------ -----------
P_ANDY                    P1                                         1 USERS                          10
P_ANDY                    P2                                         2 USERS                          20
P_ANDY                    P3                                         3 USERS                          30
P_ANDY                    P4                                         4 USERS                          40
SQL>  
ALTER TABLE P_ANDY  SPLIT PARTITION P4 INTO
(PARTITION p5 VALUES LESS THAN (33),
PARTITION p6 VALUES LESS THAN (36), PARTITION P4) UPDATE GLOBAL INDEXES ;
Table altered.
-- 檢視split後分割槽情況
SQL> 
select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE from user_tab_partitions where table_name='P_ANDY';
TABLE_NAME                PARTITION_NAME            PARTITION_POSITION TABLESPACE_NAME                HIGH_VALUE
------------------------- ------------------------- ------------------ ------------------------------ -------------
P_ANDY                    P1                                         1 USERS                          10
P_ANDY                    P2                                         2 USERS                          20
P_ANDY                    P3                                         3 USERS                          30
P_ANDY                    P5                                         4 USERS                          33
P_ANDY                    P6                                         5 USERS                          36
P_ANDY                    P4                                         6 USERS                          40
四、 將多個分割槽合併為一個分割槽
-- 表分割槽情況
SQL> 
select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE from user_tab_partitions where table_name='P_ANDY';
TABLE_NAME                PARTITION_NAME            PARTITION_POSITION TABLESPACE_NAME                HIGH_VALUE
------------------------- ------------------------- ------------------ ------------------------------ -------------
P_ANDY                    P1                                         1 USERS                          10
P_ANDY                    P2                                         2 USERS                          20
P_ANDY                    P3                                         3 USERS                          30
P_ANDY                    P5                                         4 USERS                          33
P_ANDY                    P6                                         5 USERS                          36
P_ANDY                    P4                                         6 USERS                          40
-- 多個分割槽合併為一個分割槽
SQL> ALTER TABLE p_andy MERGE PARTITIONS p5,p6 INTO PARTITION p4 UPDATE GLOBAL INDEXES;
ORA-14012: resulting partition name conflicts with that of an existing partition
SQL> ALTER TABLE p_andy MERGE PARTITIONS p5,p6,p4 INTO PARTITION p_merge UPDATE GLOBAL INDEXES;
Table altered.
-- 檢視merge 合併分割槽後,分割槽情況
SQL> select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE from user_tab_partitions where table_name='P_ANDY';
TABLE_NAME                PARTITION_NAME            PARTITION_POSITION TABLESPACE_NAME                HIGH_VALUE
------------------------- ------------------------- ------------------ ------------------------------ -------------
P_ANDY                    P1                                         1 USERS                          10
P_ANDY                    P2                                         2 USERS                          20
P_ANDY                    P3                                         3 USERS                          30
P_ANDY                    P_MERGE                                    4 USERS                          40



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31383567/viewspace-2139033/,如需轉載,請註明出處,否則將追究法律責任。

相關文章