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;
延迟Global INDEX 维护原理
在oracle 12之前分区删除或truncate 时带update global index 需要清理global index block中的索引条目，如dump index block 可以看到会打”D” flag, 如果索引很大，持续时间比较久， 12c(12.1.0.2)后的延迟全局索引维护，可以在做DDL时不会立即清理索引block， 但它会记录已删除的分区，仅把分区相关的Index 对象id 记录到新数据字典表，如INDEX_ORPHANED_ENTRY$， 不会记录index rowid, 而是在索引 entry rowid 中可以确认对象id，根据字典ORPHANED_ENTRIES =Y找INDEX_ORPHANED_ENTRY$中的对象，并在索引访问时筛选出其相应的索引条目, 执行计划出现
“filter(TBL$OR$IDX$PART$NUM("xxx",0,8,0,"xxx".ROWID)=1)” 内部函数， 这些筛选的索引条目称为孤立条目 ，使用 filter 过程比更新全局索引要快得多。 而索引段中的orphaned_entries通过 SYS 用户拥有的名为 PMO_DEFERRED_GIDX_MAINT_JOB 的计划程序作业进行清理，并在完成后修改ORPHANED_ENTRIES =N。
SQL> @desc INDEX_ORPHANED_ENTRY$
Name Null? Type
------------------------------- -------- ----------------------------
1 INDEXOBJ# NOT NULL NUMBER
2 TABPARTDOBJ# NOT NULL NUMBER
3 HIDDEN VARCHAR2(1)
-- 对象
SQL> @tab INDEX_ORPHANED_ENTRY
Show tables matching condition "%INDEX_ORPHANED_ENTRY%" (if schema is not specified then current user's tables only are shown)...
OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE COMPRESS
-------------------- ------------------------------ ---- ------------ ------------- --------- ------ ------ ------------------- ---------- --------
SYS INDEX_ORPHANED_ENTRY$ TAB 0 0 0 0 0 2022-10-07 08:41:08 1 DISABLED
SQL> @o
owner object_name object_type status OID D_OID CREATED LAST_DDL_TIME
------------------------- ------------------------------ -------------------- --------- ---------- ---------- ------------------- -------------------
SYS INDEX_ORPHANED_ENTRY$ TABLE VALID 401 401 2022-10-07 08:17:47 2022-10-07 08:17:47
SYS INDEX_ORPHANED_ENTRY_V$ VIEW VALID 6229 2022-10-07 08:19:55 2022-10-07 08:19:55
SYS I_INDEX_ORPHANED_ENTRY$_1 INDEX VALID 402 402 2022-10-07 08:17:47 2022-10-07 08:17:47
-- 作业
SQL>@jobs
JOB_NAME PROGRAM_NAME JOBS_STATE START_DATE NEXT_RUN_DATE ENABL
---------------------------------------- ---------------------------------------- --------------- ---------------- ---------------- -----
PMO_DEFERRED_GIDX_MAINT_JOB PMO_DEFERRED_GIDX_MAINT SCHEDULED 2022-10-08 02:00 2025-01-10 02:00 TRUE
SQL>select job_name, comments
from dba_scheduler_jobs
where job_name='PMO_DEFERRED_GIDX_MAINT_JOB';
COMMENTS
----------------------------------------
Oracle defined automatic index cleanup
for partition maintenance operations
with deferred global index maintenance
-- 参数
SQL> @pd "global%index"
Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
4173 104D _online_pmo_global_index_method CLEANUP ONLY Unique global index method for partition maintenance DDLs
4174 104E _online_pmo_global_index_flags 0 flags for global indexes during partition maintenance DDLs
4555 11CB _pdml_gim_sampling 5000 control separation of global index maintenance for PDML
4816 12D0 _globalindex_pnum_filter_enabled TRUE enables filter for global index with partition extended syntax
4965 1365 _px_gim_factor 100 weighted autodop global index maintenance factor
4996 1384 _part_redef_global_index_update TRUE online partition redefinition update global indexes
5032 13A8 _fast_index_maintenance TRUE fast global index maintenance during PMOPs
7 rows selected.
# 手动维护索引
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 并延迟索引维护.
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。