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.