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.