Oracle 18c New Feature – Online Partitioning Strategy Modification

In Oracle 12c Release 2, a new feature was added whereby we could convert a non-partitioned table to a partitioned table with a MODIFY clause added to the ALTER TABLE SQL statement. In addition, the operation could be performed online enabling concurrent DML operations while the conversion was being performed.

This was a very good feature in those cases where we needed to enable partitioning for a particular table but could not afford the downtime for performing the conversion.

A new feature has been added in Oracle 18c where we now also change the partitioning strategy online of a table via the ALTER TABLE MODIFY PARTITION SQL statement.

For example we could change the partitioning strategy for a table from say hash partitioning to range partitioning and the operation can also be performed online without impacting DML operations on the table.

Indexes are also maintained as part of the table modification.

This new feature enable us to adapt the partitioning method or strategy for a table based on changing business or application requirements without requiring any associated downtime for making the change.

Let us have a look at this feature via a simple example.

 
Create a non-Partitioned table
 

create table emp(
  empno    number(4,0),
  ename    varchar2(10),
  job      varchar2(9),
  mgr      number(4,0),
  hiredate date,
  sal      number(7,2),
  comm     number(7,2),
  deptno   number(2,0)
  );

create index emp_ind on emp (empno);

insert into emp
values(
 7839, 'KING', 'PRESIDENT', null,
 to_date('17-11-2011','dd-mm-yyyy'),
 5000, null, 10
);
insert into emp
values(
 7698, 'BLAKE', 'MANAGER', 7839,
 to_date('1-5-2011','dd-mm-yyyy'),
 2850, null, 30
);
insert into emp
values(
 7782, 'CLARK', 'MANAGER', 7839,
 to_date('9-6-2011','dd-mm-yyyy'),
 2450, null, 10
);
insert into emp
values(
 7566, 'JONES', 'MANAGER', 7839,
 to_date('2-4-2011','dd-mm-yyyy'),
 2975, null, 20
);
insert into emp
values(
 7788, 'SCOTT', 'ANALYST', 7566,
 to_date('13-JUL-2014','dd-mon-yyyy') - 85,
 3000, null, 20
);
insert into emp
values(
 7902, 'FORD', 'ANALYST', 7566,
 to_date('3-12-2011','dd-mm-yyyy'),
 3000, null, 20
);
insert into emp
values(
 7369, 'SMITH', 'CLERK', 7902,
 to_date('17-12-2012','dd-mm-yyyy'),
 800, null, 20
);
insert into emp
values(
 7499, 'ALLEN', 'SALESMAN', 7698,
 to_date('20-2-2011','dd-mm-yyyy'),
 1600, 300, 30
);
insert into emp
values(
 7521, 'WARD', 'SALESMAN', 7698,
 to_date('22-2-2011','dd-mm-yyyy'),
 1250, 500, 30
);
insert into emp
values(
 7654, 'MARTIN', 'SALESMAN', 7698,
 to_date('28-9-2011','dd-mm-yyyy'),
 1250, 1400, 30
);
insert into emp
values(
 7844, 'TURNER', 'SALESMAN', 7698,
 to_date('8-9-2011','dd-mm-yyyy'),
 1500, 0, 30
);
insert into emp
values(
 7655, 'ADAMS', 'CLERK', 7788,
 to_date('13-JUL-2014', 'dd-mon-yyyy'),
 1100, null, 20
);
insert into emp
values(
 7900, 'JAMES', 'CLERK', 7698,
 to_date('3-12-2011','dd-mm-yyyy'),
 950, null, 30
);

insert into emp
values(
 7934, 'MILLER', 'CLERK', 7782,
 to_date('23-1-2013','dd-mm-yyyy'),
 1300, null, 10
);

commit;

 
Convert non-Partitioned table to Partitioned table ONLINE
 

SQL> ALTER TABLE hr.emp
MODIFY 
PARTITION BY RANGE (HIREDATE) 
(PARTITION p1 VALUES LESS THAN (TO_DATE('01-JAN-2012', 'DD-MON-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('01-JAN-2013', 'DD-MON-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('01-JAN-2014', 'DD-MON-YYYY')),
PARTITION p4 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
PARTITION p5 VALUES LESS THAN (MAXVALUE))
 ONLINE 
 INCLUDING INDEXES;    

Table altered.

SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMP';

PARTITION_NAME
--------------------------------------------------------------------------------
P1
P2
P3
P4
P5

 
Convert RANGE Partitioned table to HASH partitioned table ONLINE
 

SQL> ALTER TABLE hr.emp
MODIFY 
PARTITION BY HASH (JOB)
PARTITIONS 5; 

Table altered.

SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMP';

PARTITION_NAME
--------------------------------------------------------------------------------
SYS_P288
SYS_P289
SYS_P290
SYS_P291
SYS_P292


SQL> select count(*) from emp partition (SYS_P289);

  COUNT(*)
----------
	10

SQL> select count(*) from emp partition (SYS_P290);

  COUNT(*)
----------
	 4