concept
Nonpartitioned tables can be converted to partitioned tables online. Indexes are maintained as part of this operation and can be partitioned as well. The conversion has no impact on the ongoing DML operations. You can convert non partitioned tables to online partitioned tables. Indexes are maintained as part of the operation and can also be partitioned. The transformation has no effect on the DML operation in progress. The online conversion of a nonpartitioned table to a partitioned table enables any application to adopt partitioning without application downtime. Customers can adopt partitioning for any system and evolve tables as needed to benefit from the partitioning for large tables. Converting non partitioned tables online to partitioned tables enables any application to adopt partitions without using application downtime. Customers can benefit from partitioning large tables by partitioning any system and evolving tables as needed. Restrictions: There are some limitations associated with this feature. It cannot be used to split an index ordered table (IOT). If the table has a domain index, it cannot be used. You can only convert tables to reference partitioned child tables in offline mode.
Experiment
1 Create tables and insert data, create indexes
[oracle@host1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 26 17:51:26 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> DROP TABLE t1 PURGE;
DROP TABLE t1 PURGE
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE t1 (
id NUMBER,
description VARCHAR2(50),
created_date DATE,
CONSTRAINT t1_pk PRIMARY KEY (id)
);
Table created.
SQL> CREATE INDEX t1_created_date_idx ON t1(created_date);SQL>
Index created.
SQL> 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 <= 1000;
1000 rows created.
SQL> COMMIT;
Commit complete.
2 We can see that the data is allocated over three time periods.
SQL> SELECT created_date, COUNT(*)
FROM t1
GROUP BY created_date
ORDER BY 1;
CREATED_DATE COUNT(*)
------------ ----------
01-JAN-15 322
01-JAN-16 355
01-JAN-17 323
3 Partition table We can use ALTER TABLE ... MODIFY Convert tables to partitioned tables. Here are some basic examples of this operation. Adding online keywords allows operations to be done online.
3.1 Basic offline operations.
SQL> 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'))
);
Table altered.
SQL> COLUMN table_name FORMAT A20
SQL> COLUMN partition_name FORMAT A20
SQL> SELECT table_name, partition_name
FROM user_tab_partitions where TABLE_NAME ='T1'
ORDER BY 1,2;
TABLE_NAME PARTITION_NAME
-------------------- --------------------
T1 T1_PART_2015
T1 T1_PART_2016
T1 T1_PART_2017
3.2 Online partition table
SQL> 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; 6
Table altered.
SQL> COLUMN table_name FORMAT A20
SQL> COLUMN partition_name FORMAT A20
SQL> SELECT table_name, partition_name
FROM user_tab_partitions where TABLE_NAME ='T1'
ORDER BY 1,2; 2 3
TABLE_NAME PARTITION_NAME
-------------------- --------------------
T1 T1_PART_2015
T1 T1_PART_2016
T1 T1_PART_2017
3.3 Online operation, modify index partition.
SQL> 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
);
Table altered.
--After running the last example, we can see the new partition for the table and partition index
SQL> COLUMN table_name FORMAT A20
SQL> COLUMN table_name FORMAT A20
SQL> SELECT table_name, partition_name
FROM user_tab_partitions where TABLE_NAME ='T1'
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
SQL> COLUMN partition_name FORMAT A20
SQL> SELECT index_name, partition_name, status
FROM user_ind_partitions where INDEX_NAME='T1_CREATED_DATE_IDX'
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
4 Combined partition table
//The original table can also use ALTER table for composite partition Modify the command. In this case, we convert the original table to a range hash partitioned table.
SQL> 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
);
Table altered.
//You can use the following query to display the subpartitions of tables and partition indexes.
SQL> COLUMN table_name FORMAT A20
SQL> COLUMN partition_name FORMAT A20
SQL> COLUMN subpartition_name FORMAT A20
SQL> SELECT table_name, partition_name, subpartition_name
FROM user_tab_subpartitions where TABLE_NAME ='T1'
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
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
-------------------- -------------------- --------------------
T1 T1_PART_2017 T1_SUB_PART_2017_4
12 rows selected.
SQL> COLUMN index_name FORMAT A20
SQL> COLUMN partition_name FORMAT A20
SQL> COLUMN subpartition_name FORMAT A20
SQL> SELECT index_name, partition_name, subpartition_name, status
FROM user_ind_subpartitions where INDEX_NAME='T1_CREATED_DATE_IDX'
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
INDEX_NAME PARTITION_NAME SUBPARTITION_NAME STATUS
-------------------- -------------------- -------------------- --------
T1_CREATED_DATE_IDX T1_PART_2017 T1_SUB_PART_2017_4 USABLE
12 rows selected.Reference documents: