Tags

,

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.