Shulou(Shulou.com)06/01 Report--

Before the Oracle12.2 version, if you want to convert a non-partition table into a partition table, there are several common methods: 1, build the partition table and then insert into select insert the data into the partition table; 2, use the online redefinition (DBMS_REDEFINITION) method. Their currency is: first, if there are frequent DML operations on the table, especially update operations, you need to stop business to do the conversion. The second method can operate online and does not need to stop business, but the operation steps are complicated and may make mistakes.

A new feature is provided in the Oracle12cR2 version that converts a non-partitioned table to a partitioned table with a single statement, with the following syntax:

ALTER TABLE table_name MODIFY table_partitioning_clauses [filter_condition] [ONLINE] [UPDATE INDEXES [(index {local_partitioned_index | global_partitioned_index | GLOBAL} [, index {local_partitioned_index | global_partitioned_index | GLOBAL}]. )]]

Let's test this new feature.

1. Create the test table and related indexes, and check the status

Zx@ORA12C > create table emp as select * from scott.emp;Table created.zx@ORA12C > create index idx_emp_no on emp (empno); Index created.zx@ORA12C > create index idx_emp_job on emp (job); Index created.zx@ORA12C > col table_name for a30zx@ORA12C > col index_name for a30zx@ORA12C > select table_name,partitioned from user_tables where table_name='EMP' TABLE_NAME PAR---EMP NOzx@ORA12C > 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 alter table statement to perform partition table conversion operation

Zx@ORA12C > alter table emp modify 2 partition by range (deptno) interval (10) 3 (partition p1 values less than (10), 4 partition p2 values less than (20) 5) online 6; Table altered.

3. View the status of current tables and indexes

Zx@ORA12C > select table_name,partitioned from user_tables where table_name='EMP';TABLE_NAME PAR---EMP YESzx@ORA12C > select index_name,partitioned,status from user_indexes where table_name='EMP' INDEX_NAME PAR STATUS---IDX_EMP_NO NO VALIDIDX_EMP_JOB NO VALIDzx@ORA12C > 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 has been converted to a partitioned index, but the index state is normal.

4. If you want to convert the index while converting the table, you can use the UPDATE INDEXES clause.

Zx@ORA12C > alter table emp modify 2 partition by range (deptno) interval (10) 3 (partition p1 values less than (10), 4 partition p2 values less than (20) 5) online 6 update indexes 7 (idx_emp_no local) 8; Table altered.zx@ORA12C > col table_name for a30zx@ORA12C > col index_name for a30zx@ORA12C > select table_name,partitioned from user_tables where table_name='EMP' TABLE_NAME PAR---EMP YESzx@ORA12C > 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 VALIDzx@ORA12C > select table_name,partition_name from user_tab_partitions where table_name='EMP' TABLE_NAME PARTITION_NAME---EMP P1EMP P2EMP SYS _ P403EMP SYS_P404zx@ORA12C > 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

Judging from the above execution results, not only the table EMP is converted to a partitioned table, but the index IDX_EMP_NO also converts the partitioned index, and all indexes are in a normal state.

Here are some notes in the official documentation:

When using the UPDATE INDEXES clause, note the following.

This clause can be 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 are maintained both for the online and offline conversion to a partitioned table.

This clause cannot change the columns on which the original list of indexes are defined.

This clause cannot change the uniqueness property of the index or any other index property.

If you do not specify the tablespace for any of the indexes, then the following tablespace defaults apply.

Local indexes after the conversion collocate with the table partition.

Global indexes after the conversion reside in the same tablespace of the original global index on the non-partitioned table.

If you do not specify the INDEXES clause or the INDEXES clause does not specify all the indexes on the original non-partitioned 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 are converted to local partitioned indexes.

Prefixed means that the partition key columns are included in the index definition, but the index definition is not limited to including the partitioning keys only.

Bitmap indexes become local partitioned indexes, regardless whether they are prefixed or not.

Bitmap indexes must always be local partitioned indexes.

The conversion operation cannot be performed if there are domain indexes.

Reference: http://docs.oracle.com/database/122/VLDBG/evolve-nopartition-table.htm#VLDBG-GUID-5FDB7D59-DD05-40E4-8AB4-AF82EA0D0FE5

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.