8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Online Conversion of a Non-Partitioned Table to a Partitioned Table in Oracle Database 12c Release 2 (12.2)

In previous releases you could partition a non-partitioned table using EXCHANGE PARTITION or DBMS_REDEFINITION in an "almost online" manner, but both methods require multiple steps. Oracle Database 12c Release 2 makes it easier than ever to convert a non-partitioned table to a partitioned table, requiring only a single command and no downtime.

Related articles.

Setup

Create and populate a test table. You will need to repeat this between each test.

DROP TABLE t1 PURGE;

CREATE TABLE t1 (
  id           NUMBER,
  description  VARCHAR2(50),
  created_date DATE,
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

CREATE INDEX t1_created_date_idx ON t1(created_date);

INSERT INTO t1
SELECT level,
       'Description for ' || level,
       ADD_MONTHS(TO_DATE('01-JAN-2017', 'DD-MON-YYYY'), -TRUNC(DBMS_RANDOM.value(1,4)-1)*12)
FROM   dual
CONNECT BY level <= 10000;
COMMIT;

We can see the data is spread across three years.

SELECT created_date, COUNT(*)
FROM   t1
GROUP BY created_date
ORDER BY 1;

CREATED_D   COUNT(*)
--------- ----------
01-JAN-15       3340
01-JAN-16       3290
01-JAN-17       3370

SQL>

Partition a Table

We can convert the table to a partitioned table using the ALTER TABLE ... MODIFY command. Here are some basic examples of this operation. Adding the ONLINE keyword allows the operation to be completed online.

-- Basic offline operation.
ALTER TABLE t1 MODIFY
  PARTITION BY RANGE (created_date) (
    PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),
    PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
    PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY'))
  );

-- Online operation.
ALTER TABLE t1 MODIFY
  PARTITION BY RANGE (created_date) (
    PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),
    PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
    PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY'))
  ) ONLINE;

-- Online operation with modification of index partitioning.
ALTER TABLE t1 MODIFY
  PARTITION BY RANGE (created_date) (
    PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),
    PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
    PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY'))
  ) ONLINE
  UPDATE INDEXES
  (
    t1_pk GLOBAL,
    t1_created_date_idx LOCAL
  );

After running the last example we can see the new partitions for the table and partitioned index.

COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20

SELECT table_name, partition_name
FROM   user_tab_partitions
ORDER BY 1,2;

TABLE_NAME           PARTITION_NAME
-------------------- --------------------
T1                   T1_PART_2015
T1                   T1_PART_2016
T1                   T1_PART_2017

SQL>


COLUMN index_name FORMAT A20
COLUMN partition_name FORMAT A20

SELECT index_name, partition_name, status
FROM   user_ind_partitions
ORDER BY 1,2;

INDEX_NAME           PARTITION_NAME       STATUS
-------------------- -------------------- --------
T1_CREATED_DATE_IDX  T1_PART_2015         USABLE
T1_CREATED_DATE_IDX  T1_PART_2016         USABLE
T1_CREATED_DATE_IDX  T1_PART_2017         USABLE

SQL>

Composite Partition (Sub-Partition) a Table

The original table can also be composite-partitioned using the ALTER TABLE ... MODIFY command. In this example we convert the original table to a range-hash partitioned table.

ALTER TABLE t1 MODIFY
  PARTITION BY RANGE (created_date) SUBPARTITION BY HASH (id)(
    PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')) (
      SUBPARTITION t1_sub_part_2015_1,
      SUBPARTITION t1_sub_part_2015_2,
      SUBPARTITION t1_sub_part_2015_3,
      SUBPARTITION t1_sub_part_2015_4
    ),
    PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')) (
      SUBPARTITION t1_sub_part_2016_1,
      SUBPARTITION t1_sub_part_2016_2,
      SUBPARTITION t1_sub_part_2016_3,
      SUBPARTITION t1_sub_part_2016_4
    ),
    PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')) (
      SUBPARTITION t1_sub_part_2017_1,
      SUBPARTITION t1_sub_part_2017_2,
      SUBPARTITION t1_sub_part_2017_3,
      SUBPARTITION t1_sub_part_2017_4
    )
  ) ONLINE
  UPDATE INDEXES
  (
    t1_pk GLOBAL,
    t1_created_date_idx LOCAL
  );

The sub-partitions of the table and partitioned index can be displayed using the following queries.

COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20

SELECT table_name, partition_name, subpartition_name
FROM   user_tab_subpartitions
ORDER BY 1,2, 3;

TABLE_NAME           PARTITION_NAME       SUBPARTITION_NAME
-------------------- -------------------- --------------------
T1                   T1_PART_2015         T1_SUB_PART_2015_1
T1                   T1_PART_2015         T1_SUB_PART_2015_2
T1                   T1_PART_2015         T1_SUB_PART_2015_3
T1                   T1_PART_2015         T1_SUB_PART_2015_4
T1                   T1_PART_2016         T1_SUB_PART_2016_1
T1                   T1_PART_2016         T1_SUB_PART_2016_2
T1                   T1_PART_2016         T1_SUB_PART_2016_3
T1                   T1_PART_2016         T1_SUB_PART_2016_4
T1                   T1_PART_2017         T1_SUB_PART_2017_1
T1                   T1_PART_2017         T1_SUB_PART_2017_2
T1                   T1_PART_2017         T1_SUB_PART_2017_3
T1                   T1_PART_2017         T1_SUB_PART_2017_4

SQL>


COLUMN index_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20

SELECT index_name, partition_name, subpartition_name, status
FROM   user_ind_subpartitions
ORDER BY 1,2;

INDEX_NAME           PARTITION_NAME       SUBPARTITION_NAME    STATUS
-------------------- -------------------- -------------------- --------
T1_CREATED_DATE_IDX  T1_PART_2015         T1_SUB_PART_2015_1   USABLE
T1_CREATED_DATE_IDX  T1_PART_2015         T1_SUB_PART_2015_2   USABLE
T1_CREATED_DATE_IDX  T1_PART_2015         T1_SUB_PART_2015_3   USABLE
T1_CREATED_DATE_IDX  T1_PART_2015         T1_SUB_PART_2015_4   USABLE
T1_CREATED_DATE_IDX  T1_PART_2016         T1_SUB_PART_2016_1   USABLE
T1_CREATED_DATE_IDX  T1_PART_2016         T1_SUB_PART_2016_2   USABLE
T1_CREATED_DATE_IDX  T1_PART_2016         T1_SUB_PART_2016_4   USABLE
T1_CREATED_DATE_IDX  T1_PART_2016         T1_SUB_PART_2016_3   USABLE
T1_CREATED_DATE_IDX  T1_PART_2017         T1_SUB_PART_2017_1   USABLE
T1_CREATED_DATE_IDX  T1_PART_2017         T1_SUB_PART_2017_3   USABLE
T1_CREATED_DATE_IDX  T1_PART_2017         T1_SUB_PART_2017_2   USABLE
T1_CREATED_DATE_IDX  T1_PART_2017         T1_SUB_PART_2017_4   USABLE

SQL>

Restrictions

There are some restrictions associated with this functionality.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.