ORACLE 12.2 allows the conversion of a non partitioned table to a partitioned table using the ALTER TABLE command. The whole conversion can be done “online” meaning, that during the conversion DML operations against the table are allowed.
Preparation:
-- ============================================================================= -- Create a non partitioned Table -- ============================================================================= CREATE TABLE emp_dept AS SELECT e.empno ,e.ename ,e.job ,e.mgr ,e.hiredate ,e.sal ,e.comm ,d.deptno ,d.dname ,d.loc FROM emp e JOIN dept d ON (e.deptno = d.deptno); -- ============================================================================= -- Add 2 indexes to the table -- ============================================================================= CREATE UNIQUE INDEX emp_dept_empno_idx ON emp_dept(empno); CREATE INDEX emp_dept_deptno_empno_idx ON emp_dept (deptno, empno);
ALTER TABLE MODIFY:
In a first step I try to create a list partitioned table having a global and a local index.
-- ============================================================================= -- ALTER TABLE MODIFY -- ============================================================================= ALTER TABLE emp_dept MODIFY PARTITION BY LIST (deptno) ( PARTITION P_DEPT10 VALUES (10) ,PARTITION P_DEPT20 VALUES (20) ,PARTITION P_DEPT30 VALUES (30) ,PARTITION P_DEPT40 VALUES (40) ,PARTITION P_DEFAULT VALUES (DEFAULT) ) ONLINE UPDATE INDEXES ( emp_dept_empno_idx GLOBAL ,emp_dept_deptno_empno_idx LOCAL); -- ============================================================================= -- Check partitions -- ============================================================================= SELECT table_name, partition_name FROM user_tab_partitions WHERE table_name = 'EMP_DEPT'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ EMP_DEPT P_DEFAULT EMP_DEPT P_DEPT10 EMP_DEPT P_DEPT20 EMP_DEPT P_DEPT30 EMP_DEPT P_DEPT40 -- ============================================================================= -- Check indexes -- ============================================================================= SELECT index_name, uniqueness, status, partitioned FROM user_indexes WHERE table_name = 'EMP_DEPT'; INDEX_NAME UNIQUENES STATUS PARTITIONED ------------------------------ --------- -------- --------------- EMP_DEPT_DEPTNO_EMPNO_IDX NONUNIQUE N/A YES EMP_DEPT_EMPNO_IDX UNIQUE VALID NO SELECT index_name, uniqueness, status, partitioned FROM user_indexes WHERE table_name = 'EMP_DEPT'; INDEX_NAME PARTITION_NAME STATUS ------------------------------ ------------------------------ -------- EMP_DEPT_DEPTNO_EMPNO_IDX P_DEFAULT USABLE EMP_DEPT_DEPTNO_EMPNO_IDX P_DEPT10 USABLE EMP_DEPT_DEPTNO_EMPNO_IDX P_DEPT20 USABLE EMP_DEPT_DEPTNO_EMPNO_IDX P_DEPT30 USABLE EMP_DEPT_DEPTNO_EMPNO_IDX P_DEPT40 USABLE
How about subpartitioning?
Now, let’s try to modify a table and create partitions and subpartitions from a non partitioned table. The base will be the same emp_dept table with the same indexes as in the previous example.
-- ============================================================================= -- ALTER TABLE MODIFY -- ============================================================================= ALTER TABLE emp_dept MODIFY PARTITION BY RANGE (hiredate) SUBPARTITION BY LIST (deptno) ( PARTITION hiredate_p1 VALUES LESS THAN (date '1990-01-01') ( SUBPARTITION hiredate_p1_10 VALUES (10) ,SUBPARTITION hiredate_p1_20 VALUES (20) ,SUBPARTITION hiredate_p1_30 VALUES (30)) ,PARTITION hiredate_p2 VALUES LESS THAN (date '2000-01-01') ( SUBPARTITION hiredate_p2_10 VALUES (10) ,SUBPARTITION hiredate_p2_20 VALUES (20) ,SUBPARTITION hiredate_p2_30 VALUES (30)) ) ONLINE UPDATE INDEXES ( emp_dept_empno_idx GLOBAL ,emp_dept_deptno_empno_idx LOCAL); -- ============================================================================= -- Check partitions -- ============================================================================= SELECT utp.table_name, utp.partition_name, utsp.subpartition_name FROM user_tab_partitions utp JOIN user_tab_subpartitions utsp ON (utsp.partition_name = utp.partition_name) WHERE utp.table_name = 'EMP_DEPT' ORDER BY utp.partition_name, utsp.subpartition_name; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME ---------------------------- -------------------------- ------------------------- EMP_DEPT HIREDATE_P1 HIREDATE_P1_10 EMP_DEPT HIREDATE_P1 HIREDATE_P1_20 EMP_DEPT HIREDATE_P1 HIREDATE_P1_30 EMP_DEPT HIREDATE_P2 HIREDATE_P2_10 EMP_DEPT HIREDATE_P2 HIREDATE_P2_20 EMP_DEPT HIREDATE_P2 HIREDATE_P2_30 -- ============================================================================= -- Check indexes -- ============================================================================= SELECT index_name, uniqueness, status, partitioned FROM user_indexes WHERE table_name = 'EMP_DEPT'; INDEX_NAME UNIQUENES STATUS PARTITIONED ------------------------------ --------- -------- --------------- EMP_DEPT_DEPTNO_EMPNO_IDX NONUNIQUE N/A YES EMP_DEPT_EMPNO_IDX UNIQUE VALID NO SELECT uip.index_name, uip.partition_name, uip.subpartition_name, uip.status FROM user_ind_subpartitions uip JOIN user_indexes ui ON (ui.index_name = uip.index_name) WHERE ui.table_name = 'EMP_DEPT'; INDEX_NAME PARTITION_NAME SUBPARTITION_NAME STATUS ------------------------------ -------------------- -------------------- -------- EMP_DEPT_DEPTNO_EMPNO_IDX HIREDATE_P1 HIREDATE_P1_10 USABLE EMP_DEPT_DEPTNO_EMPNO_IDX HIREDATE_P1 HIREDATE_P1_20 USABLE EMP_DEPT_DEPTNO_EMPNO_IDX HIREDATE_P1 HIREDATE_P1_30 USABLE EMP_DEPT_DEPTNO_EMPNO_IDX HIREDATE_P2 HIREDATE_P2_10 USABLE EMP_DEPT_DEPTNO_EMPNO_IDX HIREDATE_P2 HIREDATE_P2_20 USABLE EMP_DEPT_DEPTNO_EMPNO_IDX HIREDATE_P2 HIREDATE_P2_30 USABLE
Conclusion:
Everything worked as expected.