首页 > 代码库 > Oracle 12C 新特性之表分区带 异步全局索引异步维护（一次add、truncate、drop、spilt、merge多个分区）
Oracle 12C 新特性之表分区带 异步全局索引异步维护（一次add、truncate、drop、spilt、merge多个分区）
实验准备：
-- 创建实验表
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,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
Oracle 12C 新特性之表分区带 异步全局索引异步维护（一次add、truncate、drop、spilt、merge多个分区）