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 required 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.

• Setup
• Partition a Table
• Composite Partition (Sub-Partition) a Table
• Restrictions

Related articles.

• Online Conversion of a Non-Partitioned Table to a Partitioned Table in Oracle 12.2 Onward
• Partitioning an Existing Table using EXCHANGE PARTITION
• Partitioning an Existing Table using DBMS_REDEFINITION
• All Partitioning Articles
• Partitioning Enhancements in Oracle Database 12c Release 2 (12.2)

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 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, meaning most DML operations can continue during the conversion.

-- 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;

We gather statistics and check the table partitions. We can see the data has been split between the partitions as expected.

-- Gather statistics.
EXEC DBMS_STATS.gather_table_stats(NULL, 'T1');

-- Check table partitions.
SELECT table_name, partition_name, num_rows
FROM   user_tab_partitions
ORDER BY 1,2;

TABLE_NAME           PARTITION_NAME         NUM_ROWS
-------------------- -------------------- ----------
T1                   T1_PART_2015               3340
T1                   T1_PART_2016               3290
T1                   T1_PART_2027               3370

SQL>

When we check the indexes we see the index on the CREATED_DATE column has been converted to a locally partitioned index. By default all prefixed indexes, an index with the partition key in the column list, will be converted to locally partitioned indexes. All indexes are left in a valid state at the end of the operation.

-- Check indexes.
SELECT index_name, partitioned, status
FROM   user_indexes
ORDER BY 1;

INDEX_NAME           PARTITIONED STATUS
-------------------- ----------- --------
T1_CREATED_DATE_IDX  YES         N/A
T1_PK                NO          VALID

SQL>

-- Check index partitions.
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>

The offline operation is similar, but we omit the ONLINE keyword. The outcome will be the same as the online operation, but DML will not be available during the operation.

-- 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'))
  );

We can influence the index conversion using the UPDATE INDEXES clause. This can indicate the type of index to be rebuilt as part of the operation, as well as some storage parameters. In the following example we want the index on the CREATED_DATE column to remain as a global index.

-- 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 GLOBAL
  );

After running the last example we can see the indexes as both non-partitioned, and as a result there are no index partitions.

-- Check indexes.
SELECT index_name, partitioned, status
FROM   user_indexes
ORDER BY 1;

INDEX_NAME           PARTITIONED STATUS
-------------------- ----------- --------
T1_CREATED_DATE_IDX  NO          VALID
T1_PK                NO          VALID

SQL>

-- Check index partitions.
SELECT index_name, partition_name, status
FROM   user_ind_partitions
ORDER BY 1,2;

no rows selected

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.

• It can't be used to partition an index-organized table (IOT).
• It can't be used if the table has a domain index.
• You can only convert a table to a reference-partitioned child table in offline mode.

For more information see:

• Converting a Non-Partitioned Table to a Partitioned Table
• ALTER TABLE
• Online Conversion of a Non-Partitioned Table to a Partitioned Table in Oracle 12.2 Onward
• Partitioning an Existing Table using EXCHANGE PARTITION
• Partitioning an Existing Table using DBMS_REDEFINITION
• All Partitioning Articles
• Partitioning Enhancements in Oracle Database 12c Release 2 (12.2)

Hope this helps. Regards Tim...

Back to the Top.

Created: 2017-03-15  Updated: 2020-04-19

Contact Us

Home | Articles | Scripts | Blog | Certification | Videos | Misc | About

About Tim Hall
Copyright & Disclaimer