首页 » ORACLE, ORACLE [C]系列 » Oracle 12c New Feature: PARTITION 增强(一) 多分区维护

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 并延迟索引维护.

打赏

,

对不起,这篇文章暂时关闭评论。