在数据库维护中Partition 到处可见, 经常要add, drop, split, merge, truncate partition, 比如我们现在的数据库环境一套库每月要增加上万的分区, oracle 在新版本中对于partition一再增强, 稍后会对12C partition相关技术整理, 这篇日志将演示12c 在分区维护上的增强.
# 一次add多个分区
# 一次truncate多个分区
# 一次drop 多个分区
# 创建一个全局非分区索引
TIP:
DBA/ALL/USER_INDEXES OR DBA/ALL/USER_IND_PARTITIONS中在12C增加了列ORPHANED_ENTRIES ,这列表示是否全局索引是否当前保含过期条目(索引有表不存在的数据),是因为我们前段做了truncate partition后, 延迟索引维护的的新特性引入
.该列可能存在3个值:
• YES: 该索引存在orphaned(过期游离)条目
• NO: 该索引不存在orphaned(过期游离)条目
• N/A: 不适用的类型如非分区表索引或local 索引
对于索引我们可以手动维护该索引, 清理当前索引中存在的游离条目. 方法有三:
当然也可以手动维护时使用并行,加速维护.对于上面维护方法1和以前类似,不再描;
方法2:
方法3:
# 手动维护索引
# 一次spilt多个分区
Summary:
12c中可以一次维护多个分区, 并在维护分区时可以使用update global index 并延迟索引维护.
[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 并延迟索引维护.
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
