Oracle 12c New Feature: PARTITION 增强(一) 多分区维护
在数据库维护中Partition 到处可见, 经常要add, drop, split, merge, truncate partition, 比如我们现在的数据库环境一套库每月要增加上万的分区, oracle 在新版本中对于partition一再增强, 稍后会对12C partition相关技术整理, 这篇日志将演示12c 在分区维护上的增强.
[oracle@anbob ~]$ sqlplus anbob/anbob SQL*Plus: Release 12.2.0.0.0 Beta on Wed Dec 28 15:54:49 2016 Copyright (c) 1982, 2015, Oracle. All rights reserved. Last Successful login time: Sat Dec 17 2016 16:44:36 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.0.1 - 64bit Beta With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options USERNAME INST_NAME HOST_NAME SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR -------------------- -------------------- ------------------------- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ---------------- ANBOB PDBORCL-orcl anbob 88 21871 12.2.0.0.1 20161228 13980 48 13978 00000000BE24AD28 00000000BFCC34C8 SQL> CREATE TABLE ANBOB_T1 (ID number(8), NAME varchar2(40), sal number (6)) 3 PARTITION BY RANGE (sal) 4 (PARTITION p1 VALUES LESS THAN (1000), 5 PARTITION p2 VALUES LESS THAN (2000) 6 ); Table created.
# 一次add多个分区
SQL> ALTER TABLE ANBOB_T1 ADD
2 PARTITION p3 VALUES LESS THAN (3000),
3 PARTITION p4 VALUES LESS THAN (4000),
4 PARTITION p5 VALUES LESS THAN (5000);
Table altered.
SQL> @tabpart ANBOB_T1
TABLE_OWNE TABLE_NAME POS COM PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE_RAW
---------- -------------------- ---------- --- -------------------- ---------- ------------------ ---------------
ANBOB ANBOB_T1 1 NO P1 0 1000
ANBOB ANBOB_T1 2 NO P2 0 2000
ANBOB ANBOB_T1 3 NO P3 0 3000
ANBOB ANBOB_T1 4 NO P4 0 4000
ANBOB ANBOB_T1 5 NO P5 0 5000
SQL> insert into ANBOB_T1
2 select rownum,'anbob',rownum from xmltable('1 to 4999');
4999 rows created.
SQL> commit;
Commit complete.
SQL> @gts ANBOB_T1
Gather Table Statistics for table ANBOB_T1...
PL/SQL procedure successfully completed.
SQL> @tabpart ANBOB_T1
TABLE_OWNE TABLE_NAME POS COM PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE_RAW HIGH_VALUE_LENGTH COMPRESS COMPRESS_FOR
---------- -------------------- ---------- --- -------------------- ---------- ------------------ -------------------- ----------------- -------- ------------------------------
ANBOB ANBOB_T1 1 NO P1 999 0 1000 4 DISABLED
ANBOB ANBOB_T1 2 NO P2 1000 0 2000 4 DISABLED
ANBOB ANBOB_T1 3 NO P3 1000 0 3000 4 DISABLED
ANBOB ANBOB_T1 4 NO P4 1000 0 4000 4 DISABLED
ANBOB ANBOB_T1 5 NO P5 1000 0 5000 4 DISABLED
# 一次truncate多个分区
SQL> ALTER TABLE ANBOB_T1 TRUNCATE partition p4,p5; Table truncated. SQL> @gts ANBOB_T1 Gather Table Statistics for table ANBOB_T1... PL/SQL procedure successfully completed. SQL> @tabpart ANBOB_T1 TABLE_OWNE TABLE_NAME POS COM PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE_RAW ---------- -------------------- ---------- --- -------------------- ---------- ------------------ --------------- ANBOB ANBOB_T1 1 NO P1 999 0 1000 ANBOB ANBOB_T1 2 NO P2 1000 0 2000 ANBOB ANBOB_T1 3 NO P3 1000 0 3000 ANBOB ANBOB_T1 4 NO P4 0 0 4000 ANBOB ANBOB_T1 5 NO P5 0 0 5000
# 一次drop 多个分区
SQL> ALTER TABLE ANBOB_T1 DROP partition p4,p5; Table altered. SQL> @tabpart ANBOB_T1 TABLE_OWNE TABLE_NAME POS COM PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE_RAW ---------- -------------------- ---------- --- -------------------- ---------- ------------------ ---------------- ANBOB ANBOB_T1 1 NO P1 999 0 1000 ANBOB ANBOB_T1 2 NO P2 1000 0 2000 ANBOB ANBOB_T1 3 NO P3 1000 0 3000
# 创建一个全局非分区索引
SQL> create index idx_t1_id on ANBOB_T1(id); Index created. SQL> @ind idx_t1_id Display indexes where table or index name matches %idx_t1_id%... TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC -------------- ---------------- ---------------- ---- ------------------------------ ---- ANBOB ANBOB_T1 IDX_T1_ID 1 ID INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS -------------- ---------------- ---------------- ---------- ---- -------- ---- ---- -- ---------- ANBOB ANBOB_T1 IDX_T1_ID NORMAL NO VALID NO N 2 8 # truncate partition会失全局索引失效(without update index) SQL> alter table anbob_t1 truncate partition p3; Table truncated. SQL> @ind idx_t1_id Display indexes where table or index name matches %idx_t1_id%... TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC ------------- ------------ ---------------- ---- ------------------------------ ---- ANBOB ANBOB_T1 IDX_T1_ID 1 ID INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS ------------- ------------ ---------------- ---------- ---- -------- ---- ---- -- ---------- ANBOB ANBOB_T1 IDX_T1_ID NORMAL NO UNUSABLE NO N 2 8 SQL> select index_name,ORPHANED_ENTRIES from user_indexes INDEX_NAME ORP ------------------------------ --- IDX_ORGANIZED_ID NO IDX_T1_ID NO SQL> insert into ANBOB_T1 2 select rownum,'anbob',rownum from xmltable('2000 to 2999'); 1000 rows created. SQL> insert into ANBOB_T1 2 select rownum,'anbob',to_number(COLUMN_VALUE) from xmltable('2000 to 2999'); 1000 rows created. SQL> commit; SQL> alter index IDX_T1_ID rebuild; Index altered. # whit update index SQL> ALTER TABLE ANBOB_T1 truncate partition p3 UPDATE GLOBAL INDEXES; Table truncated. SQL> @ind IDX_T1_ID Display indexes where table or index name matches %IDX_T1_ID%... TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC -------------- -------------- -------------- ---- ------------------------------ ---- ANBOB ANBOB_T1 IDX_T1_ID 1 ID INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS -------------- -------------- -------------- ---------- ---- -------- ---- ---- -- ---------- ANBOB ANBOB_T1 IDX_T1_ID NORMAL NO VALID NO N 2 11 SQL> select index_name,ORPHANED_ENTRIES from user_indexes ; INDEX_NAME ORP ------------------------------ --- IDX_ORGANIZED_ID NO IDX_T1_ID YES 2 rows selected.
TIP:
DBA/ALL/USER_INDEXES OR DBA/ALL/USER_IND_PARTITIONS中在12C增加了列ORPHANED_ENTRIES ,这列表示是否全局索引是否当前保含过期条目(索引有表不存在的数据),是因为我们前段做了truncate partition后, 延迟索引维护的的新特性引入
ORPHANED_ENTRIES =Y
.该列可能存在3个值:
• YES: 该索引存在orphaned(过期游离)条目
• NO: 该索引不存在orphaned(过期游离)条目
• N/A: 不适用的类型如非分区表索引或local 索引
对于索引我们可以手动维护该索引, 清理当前索引中存在的游离条目. 方法有三:
1, 常用的 ALTER INDEX xxx REBUILD;
2, exec DBMS_PART.CLEANUP_GIDX('[SCHEMA]','[TABLE NAME]');
3, ALTER INDEX ORDERS_GIDX_ORDERTOTAL COALESCE CLEANUP;
当然也可以手动维护时使用并行,加速维护.对于上面维护方法1和以前类似,不再描;
方法2:
SQL> alter session force parallel ddl parallel 8;
exec DBMS_PART.CLEANUP_GIDX('[SCHEMA]','[TABLE NAME]');
方法3:
ALTER INDEX ORDERS_GIDX_ORDERTOTAL COALESCE CLEANUP N;
# 手动维护索引
SQL> ALTER INDEX IDX_T1_ID COALESCE CLEANUP PARALLEL 5;
Index altered.
SQL> select index_name,ORPHANED_ENTRIES from user_indexes ;
INDEX_NAME ORP
------------------------------ ---
IDX_ORGANIZED_ID NO
IDX_T1_ID NO
2 rows selected.
SQL> @gts anbob_t1
Gather Table Statistics for table anbob_t1...
PL/SQL procedure successfully completed.
SQL> @tabpart ANBOB_T1
TABLE_OWNE TABLE_NAME POS COM PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE_RAW
---------- -------------------- ---------- --- -------------------- ---------- ------------------ ----------------
ANBOB ANBOB_T1 1 NO P1 1998 0 1000
ANBOB 2 NO P2 1001 0 2000
ANBOB 3 NO P3 1000 0 3000
3 rows selected.
# 一次spilt多个分区
SQL> ALTER TABLE ANBOB_T1 SPLIT PARTITION P3 INTO 2 (PARTITION p4 VALUES LESS THAN (2300), 3 PARTITION p5 VALUES LESS THAN (2600), PARTITION P3) UPDATE GLOBAL INDEXES ; Table altered. SQL> @ind IDX_T1_ID Display indexes where table or index name matches %IDX_T1_ID%... TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC -------------- -------------- ---------------- ---- ------------------------------ ---- ANBOB ANBOB_T1 IDX_T1_ID 1 ID INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS -------------- -------------- ---------------- ---------- ---- -------- ---- ---- -- ---------- ANBOB ANBOB_T1 IDX_T1_ID NORMAL NO VALID NO N 2 11 SQL> select index_name,ORPHANED_ENTRIES from user_indexes ; INDEX_NAME ORP ------------------------------ --- IDX_ORGANIZED_ID NO IDX_T1_ID YES 2 rows selected. SQL> @gts ANBOB_T1 Gather Table Statistics for table ANBOB_T1... PL/SQL procedure successfully completed. SQL> @tabpart ANBOB_T1 TABLE_OWNE TABLE_NAME POS COM PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE_RAW ---------- -------------------- ---------- --- -------------------- ---------- ------------------ ---------------- ANBOB ANBOB_T1 1 NO P1 1998 0 1000 ANBOB 2 NO P2 1001 0 2000 ANBOB 3 NO P4 300 0 2300 ANBOB 4 NO P5 300 0 2600 ANBOB 5 NO P3 400 0 3000 5 rows selected. SQL> alter session force parallel ddl parallel 8; Session altered. SQL> exec DBMS_PART.CLEANUP_GIDX('ANBOB','ANBOB_T1'); PL/SQL procedure successfully completed. SQL> select index_name,ORPHANED_ENTRIES from user_indexes ; INDEX_NAME ORP --------------------------- --- IDX_ORGANIZED_ID NO IDX_T1_ID NO
Summary:
12c中可以一次维护多个分区, 并在维护分区时可以使用update global index 并延迟索引维护.
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。