在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、创建测试表及相关索引，并查看状态
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
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语句，执行分区表转换操作
|
1
2
3
4
5
6
7
8
|
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、查看现在的表和索引的状态
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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
|
现在表EMP已经被转换为分区表了，索引转换为分区索引，但索引状态是正常的。
4、如果想在转换表时同时转换索引可以使用UPDATE INDEXES子句
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
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
|
从上面的执行结果来看，不仅表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
UPDATEINDEXESclause 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
INDEXESclause or theINDEXESclause 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.
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1949739，如需转载请自行联系原作者
