Before the Oracle12.2 version, if you want to convert a non-partitioned table to a partitioned table, there are several ways to do this: 1. Build the partition table and insert the data into the partition table with the INSERT into SELECT, 2, use the method of online redefinition (dbms_redefinition). Their currency is: the first method, if the table has frequent DML operations, especially the update operation, you need to stop the business to do the conversion. The second method can be operated online without the need to stop the business, but the steps are complex and may be error-prone.
A new feature is provided in the ORACLE12CR2 version, where a statement can convert a non-partitioned table to a partitioned table with the following syntax:
ALTER TABLE table_name MODIFY table_partitioning_clauses [filter_condition] [ONLINE] [UPDATE INDEXES [(Index {L Ocal_partitioned_index | Global_partitioned_index | GLOBAL} [, index {local_partitioned_index | global_partitioned_index | GLOBAL}] ...) ] ]
Let's test the new feature here.
1. Create test tables and related indexes, and view status
[email protected]>create table emp as select * from scott.emp; Table created. [Email protected]>create index idx_emp_no on emp (Empno); Index created. [Email protected]>create index idx_emp_job on emp (Job); Index created. [Email protected]>col table_name for a30[email protected]>col index_name for a30[email protected]>select table_name,partitioned from user_tables where table_name= ' EMP '; table_name par------------------------------ ---emp NO[email protected]>select index_name,partitioned,status From user_indexes where table_name= ' EMP ';index_name PAR STATUS------------------------------ --- --------idx_emp_no NO valididx_emp_job no valid
2. Use the ALTER TABLE statement to perform partition table conversion operations
[Email protected]>alter table EMP Modify 2 partition by range (DEPTNO) interval (TEN) 3 (partition P1 values Le SS Than (Ten), 4 partition P2 values less than (5) online 6; Table altered.
3. View the status of tables and indexes now
[Email protected]>select table_name,partitioned from user_tables where table_ Name= ' EMP '; table_name par------------------------------ ---emp yes[email protected]>select index_name,partitioned,status from user_indexes where table_name= ' EMP ';index_name PAR STATUS------------------------------ --- --------idx_emp_no NO Valididx_emp_job no valid[email protected]>select table_name,partition_name from user_tab_partitions Where table_name= ' EMP '; table_name partition_name------------------------------ ------------------------------ emp P1EMP P2EMP SYS_P405EMP sys_p406
Now that the table EMP has been converted to a partitioned table, the index is converted to a partitioned index, but the index state is normal.
4. If you want to convert the index at the same time when converting the table, you can use the Update indexes clause
[Email protected]>alter table emp modify 2 partition by range (Deptno) interval (Ten) 3 ( partition p1 values less than ( 4 partition), p2 values less than ( 5 ) online 6 update indexes 7 (idx_emp_no local) 8 ; Table altered. [Email protected]>col table_name for a30[email protected]>col index_name for a30[email protected]>select table_name,partitioned from user_tables where table_name= ' EMP '; table_name par------------------------------ ---emp YES[email protected]> Select index_name,partitioned,status from user_indexes where table_name= ' EMP '; INDEX_ name par status------------------------------ --- --------idx_emp_no YES N/AIDX_EMP_JOB no valid[email protected]>select table _name,partition_name from user_tab_partitions where table_name= ' EMP '; Table_name &nbSp; partition_ NAME------------------------------ ------------------------------emp P1EMP P2EMP SYS_P403EMP sys_p404[email protected]>select index_name,partition_name,status from user _ind_partitions where index_nAme= ' Idx_emp_no ';index_name PARTITION_NAME status------------------------------ -------------- ---------------- --------idx_emp_no P1 USABLEIDX_EMP_NO P2 usableidx_emp_no SYS_P403 USABLEIDX_EMP_NO SYS_P404 usable
From the above results, not only the table EMP is converted to a partitioned table, but also the index idx_emp_no is converted to the partition index, all index states are normal.
Here are some caveats in the official documentation:
When using UPDATE
INDEXES
the clause, note the following.
This clause can is used to change the partitioning state of indexes and storage properties of the indexes being converted.
The specification of the UPDATE
INDEXES
clause is optional.
Indexes is maintained both for the online and offline conversion to a partitioned table.
This clause cannot, the columns on which the original list of indexes is defined.
This clause cannot, the uniqueness property of the index, or any, of the Index property.
If you don't specify the tablespace for any of the indexes and then the following tablespace defaults apply.
If you don't specify the INDEXES
clause or the clause does not specify all the indexes on the INDEXES
original Non-partitione d table, then the following default behavior applies for all unspecified indexes.
Global partitioned indexes remain the same and retain the original partitioning shape.
non-prefixed indexes become global nonpartitioned indexes.
Prefixed indexes is converted to local partitioned indexes.
Prefixed means that the partition key columns be included in the "index definition, but the" index definition is not limite D to including the partitioning keys only.
Bitmap indexes become local partitioned indexes, regardless whether they is prefixed or not.
Bitmap indexes must always be local partitioned indexes.
The conversion operation cannot be performed if there is domain indexes.
Reference: Http://docs.oracle.com/database/122/VLDBG/evolve-nopartition-table.htm#VLDBG-GUID-5FDB7D59-DD05-40E4-8AB4-AF82EA0D0FE5
This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1949739
Oracle 12.2 New features----convert non-partitioned tables to partitioned tables online