Oracle 12.2新特性----線上把非分區錶轉為分區表

標籤：oracle   online   convert   

在Oracle12.2版本之前，如果想把一個非分區錶轉為分區表常用的有這幾種方法：1、建好分區表然後insert into select 把資料插入到分區表中；2、使用線上重定義(DBMS_REDEFINITION)的方法。它們的幣是：第一種方法，如果對錶有頻繁的DML操作，尤其是update操作，就需要停業務來做轉換。第二種方法可以線上進行操作，不需要停業務，但操作步驟比較複雜，且可能出錯。

Oracle12cR2版本中提供了一種新特性，一條語句就可以把非分區錶轉換為分區表，文法如下：

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 } ]... )                   ]  ]

下面來測試一下這個新特性

1、建立測試表及相關索引，並查看狀態

[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、使用alter table語句，執行分區錶轉換操作

[email protected]>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、查看現在的表和索引的狀態

[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

現在表EMP已經被轉換為分區表了，索引轉換為分區索引，但索引狀態是正常的。

4、如果想在轉換表時同時轉換索引可以使用UPDATE INDEXES子句

[email protected]>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.[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                     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

從上面的執行結果來看，不僅表EMP轉換為分區表，而且索引IDX_EMP_NO也轉換分區索引，所有索引狀態均正常。

下面是官方文檔裡的一些注意事項：

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.

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

本文出自 “DBA Fighting!” 部落格，請務必保留此出處http://hbxztc.blog.51cto.com/1587495/1949739

Oracle 12.2新特性----線上把非分區錶轉為分區表