在Oracle12.2版本之前，如果想把一個(gè)非分區(qū)表轉(zhuǎn)為分區(qū)表常用的有這幾種方法：1、建好分區(qū)表然后insert into select 把數(shù)據(jù)插入到分區(qū)表中；2、使用在線重定義(DBMS_REDEFINITION)的方法。它們的幣是：第一種方法，如果對(duì)表有頻繁的DML操作，尤其是update操作，就需要停業(yè)務(wù)來(lái)做轉(zhuǎn)換。第二種方法可以在線進(jìn)行操作，不需要停業(yè)務(wù)，但操作步驟比較復(fù)雜，且可能出錯(cuò)。
創(chuàng)新互聯(lián)憑借專業(yè)的設(shè)計(jì)團(tuán)隊(duì)扎實(shí)的技術(shù)支持、優(yōu)質(zhì)高效的服務(wù)意識(shí)和豐厚的資源優(yōu)勢(shì)，提供專業(yè)的網(wǎng)站策劃、網(wǎng)站設(shè)計(jì)制作、成都網(wǎng)站設(shè)計(jì)、網(wǎng)站優(yōu)化、軟件開發(fā)、網(wǎng)站改版等服務(wù)，在成都10多年的網(wǎng)站建設(shè)設(shè)計(jì)經(jīng)驗(yàn)，為成都上千中小型企業(yè)策劃設(shè)計(jì)了網(wǎng)站。
Oracle12cR2版本中提供了一種新特性，一條語(yǔ)句就可以把非分區(qū)表轉(zhuǎn)換為分區(qū)表，語(yǔ)法如下：
重慶分公司
重慶分公司
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 } ]... )
]
]下面來(lái)測(cè)試一下這個(gè)新特性
1、創(chuàng)建測(cè)試表及相關(guān)索引，并查看狀態(tài)
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 a30 zx@ORA12C>col index_name for a30 zx@ORA12C>select table_name,partitioned from user_tables where table_name='EMP'; TABLE_NAME PAR ------------------------------ --- EMP NO zx@ORA12C>select index_name,partitioned,status from user_indexes where table_name='EMP'; INDEX_NAME PAR STATUS ------------------------------ --- -------- IDX_EMP_NO NO VALID IDX_EMP_JOB NO VALID
2、使用alter table語(yǔ)句，執(zhí)行分區(qū)表轉(zhuǎn)換操作
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、查看現(xiàn)在的表和索引的狀態(tài)
zx@ORA12C>select table_name,partitioned from user_tables where table_name='EMP'; TABLE_NAME PAR ------------------------------ --- EMP YES zx@ORA12C>select index_name,partitioned,status from user_indexes where table_name='EMP'; INDEX_NAME PAR STATUS ------------------------------ --- -------- IDX_EMP_NO NO VALID IDX_EMP_JOB NO VALID zx@ORA12C>select table_name,partition_name from user_tab_partitions where table_name='EMP'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ EMP P1 EMP P2 EMP SYS_P405 EMP SYS_P406
現(xiàn)在表EMP已經(jīng)被轉(zhuǎn)換為分區(qū)表了，索引轉(zhuǎn)換為分區(qū)索引，但索引狀態(tài)是正常的。
4、如果想在轉(zhuǎn)換表時(shí)同時(shí)轉(zhuǎn)換索引可以使用UPDATE INDEXES子句
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 a30 zx@ORA12C>col index_name for a30 zx@ORA12C>select table_name,partitioned from user_tables where table_name='EMP'; TABLE_NAME PAR ------------------------------ --- EMP YES zx@ORA12C>select index_name,partitioned,status from user_indexes where table_name='EMP'; INDEX_NAME PAR STATUS ------------------------------ --- -------- IDX_EMP_NO YES N/A IDX_EMP_JOB NO VALID zx@ORA12C>select table_name,partition_name from user_tab_partitions where table_name='EMP'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ EMP P1 EMP P2 EMP SYS_P403 EMP SYS_P404 zx@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 USABLE IDX_EMP_NO P2 USABLE IDX_EMP_NO SYS_P403 USABLE IDX_EMP_NO SYS_P404 USABLE
從上面的執(zhí)行結(jié)果來(lái)看，不僅表EMP轉(zhuǎn)換為分區(qū)表，而且索引IDX_EMP_NO也轉(zhuǎn)換分區(qū)索引，所有索引狀態(tài)均正常。
下面是官方文檔里的一些注意事項(xiàng)：
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
