Category: Partitioning

Fast Split Partition

Try to split  yearly partition into monthly 12 partitions , it took more than 7 hours , and finally failed for splitting January one.

SQl>alter table CDR split partition PAR_2018 at ( 
    TO_DATE(' 2018-02-01 00:00:00','sYYYY-MM-DD HH24:MI:SS') ) 
    into ( partition par_201801, partition PAR_2018 ) update indexes;
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS4'

Elapsed: 07:19:47.81

For 11gR2, here is the faster way of splitting the partition.

1) Disable the indexes.

SQL>alter index CDR_IDX1 unusable;
SQL>alter index CDR_IDX2 unusable;
SQL>alter index CDR_IDX3 unusable;
SQL>alter index CDR_IDX4 unusable;

2) Split partition with parallel option.

SQl>alter table CDR split partition PAR_2018 at ( 
    TO_DATE(' 2018-02-01 00:00:00','sYYYY-MM-DD HH24:MI:SS') ) 
    into ( partition par_201801, partition PAR_2018 )
    parallel 8;

Table altered.

Elapsed: 00:00:43.75

3) Rebuild indexes with parallel option.

SQL> alter index CDR_IDX1 rebuild parallel 16;

Index altered.

Elapsed: 00:01:26.70

SQL> alter index CDR_IDX2 rebuild parallel 16;

Index altered.

Elapsed: 00:00:42.96

SQL> alter index CDR_IDX3 rebuild parallel 16;

Index altered.

Elapsed: 00:00:41.34

SQL> alter index CDR_IDX4 rebuild parallel 16;

Index altered.

Elapsed: 00:00:47.16



SQL>  alter index CDR_IDX1 parallel 1;

Index altered.

Elapsed: 00:00:00.01
SQL>  alter index CDR_IDX2 parallel 1;

Index altered.

Elapsed: 00:00:00.03
SQL>  alter index CDR_IDX3 parallel 1;

Index altered.

Elapsed: 00:00:00.01
SQL>  alter index CDR_IDX4  parallel 1;

Index altered.

Elapsed: 00:00:00.01

For 12c onward, split a partition into multiple partitions in one statement.

SQL>alter table CDR split partition PAR_2018 into ( 
partition par_201801 values less than TO_DATE(' 2018-02-01 00:00:00','sYYYY-MM-DD HH24:MI:SS')),
partition par_201802 values less than TO_DATE(' 2019-03-01 00:00:00','sYYYY-MM-DD HH24:MI:SS')),
partition par_201903 values less than TO_DATE(' 2019-04-01 00:00:00','sYYYY-MM-DD HH24:MI:SS')),
...
..
.
partition par_201811 values less than TO_DATE(' 2018-12-01 00:00:00','sYYYY-MM-DD HH24:MI:SS')),
partition par_201812 values less than TO_DATE(' 2019-01-01 00:00:00','sYYYY-MM-DD HH24:MI:SS'))
);
Advertisement

Fast Split Partition and Split Subpartition

Fast split partition is very useful in production environment where outage is unavailable, and no impact on partitioned tables. Please refer to the details in Fast SPLIT PARTITION and SPLIT SUBPARTITION

Here is a good example of using fast split partition operation. For some reason, table CDR is partitioned by transaction date, and the invalid CDR records created for future date with future partitions created.

SQL> select sysdate from dual;

SYSDATE
---------
24-JUL-19

TABLE_NAME    PARTITION  INTERVAL
------------- ---------- --------- ---------------------------
CDR           RANGE      NUMTODSINTERVAL(1,'DAY')

TABLE_NAME PARTITION_NAME   HIGH_VALUE                    NUM_ROWS
---------- -------------- ------------------------------ ---------
CDR        P_FIRST        TIMESTAMP' 1900-01-01 00:00:00'         0
CDR        SYS_P707668    TIMESTAMP' 2019-09-04 00:00:00' 296956241
CDR        SYS_P708049    TIMESTAMP' 2019-09-05 00:00:00'         0
CDR        SYS_P708687    TIMESTAMP' 2019-09-06 00:00:00'         0
CDR        SYS_P709207    TIMESTAMP' 2019-09-07 00:00:00'         0

After cleaned the invalid CDR records, the future partitions should be split and dropped.

SQL> alter table CDR set interval ();

Table altered.


SQL> alter table CDR drop partition SYS_P709207 update indexes;

Table altered.


SQL> alter table CDR drop partition SYS_P708687 update indexes;

Table altered.

SQL> alter table CDR drop partition SYS_P708049  update indexes;

Table altered.

SQL> alter table CDR split partition SYS_P707668 at 
     ( TO_DATE(' 2019-07-25 00:00:00','sYYYY-MM-DD HH24:MI:SS') ) 
into ( partition par_20190724, partition SYS_P707668 ) update indexes;

Table altered.


SQL> select count(*) from CDR partition ( SYS_P707668);

COUNT(*)
----------
0

SQL> alter table CDR drop partition SYS_P707668 update indexes;

Table altered.

SQL> alter table CDR set interval (NUMTODSINTERVAL(1,'DAY'));

Table altered.


TABLE_NAME  PARTITION_NAME  HIGH_VALUE                      NUM_ROWS
----------- --------------- -------------------- ---------- ---------
CDR         P_FIRST         TIMESTAMP' 1900-01-01 00:00:00'        0
CDR         PAR_20190724    TIMESTAMP' 2019-07-25 00:00:00' 297116561
CDR         SYS_P709208     TIMESTAMP' 2019-07-26 00:00:00'    123456
CDR         SYS_P709923     TIMESTAMP' 2019-07-27 00:00:00'    234567
...
..
.

Partition split is very fast, because all the records go to one partition, and the other partition is empty.

How to Find the Interval String of an Interval Partition Table

SQL> select TABLE_NAME, PARTITIONING_TYPE, INTERVAL 
     from dba_part_tables 
     where owner='TESTUSER' ;

TABLE_NAME    PARTITION_TYPE    INTERVAL
------------- ----------------- -----------------------------
TABLE1        RANGE             NUMTODSINTERVAL(1, 'DAY' )
TABLE2        RANGE             NUMTODSINTERVAL(1, 'MONTH' )
TABLE3        RANGE             NUMTODSINTERVAL(15, 'MINUTE' )
...
..
.

ORA-14758: Last partition in the range section cannot be dropped

Try to drop the last interval partition and get error “ORA-14758: Last partition in the range section cannot be dropped”.

SQL>alter table PART_TAB drop partition SYS_P3726162 update indexes;
alter table PART_TAB drop partition SYS_P3726162 update indexes
*
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped

WORKAROUND

Disable interval partition definition, then drop the last partition and re-enable the interval partition again.

SQL> alter table PART_TAB set interval();

Table altered.


SQL> alter table PART_TAB drop partition SYS_P3726162 update indexes;

Table altered.


SQL>alter table PART_TAB set INTERVAL (NUMTODSINTERVAL(1,'DAY'));

Table altered.

How to Get Partition Column Name of a Partitioned Table in Oracle

SQL> desc dba_part_key_columns

Name             Null?    Type
---------------- -------- ----------------------
OWNER                     VARCHAR2(30)
NAME                      VARCHAR2(30)
OBJECT_TYPE               CHAR(5)
COLUMN_NAME               VARCHAR2(4000)
COLUMN_POSITION           NUMBER
SQL> select OWNER, NAME, OBJECT_TYPE, COLUMN_NAME, COLUMN_POSITION 
     from   DBA_PART_KEY_COLUMNS 
     where  OWNER ='TESTUSER' 
     and    OBJECT_TYPE='TABLE';