http://www.oracleonlinux.cn/2012/09/oracle-splitting-partitions/
本文简单记录在Oracle 10g数据库上对范围分区表的Splitting Partitions测试过程和结论,并不涉及到Oracle数据库中分区技术的详细描述。
1 测试环境及平台:
OS:
1 | [root@localhost ~] # uname -rm |
2 | 2.6.18-164.el5 x86_64 |
3 | [root@localhost ~] # |
Oracle:
01 | SQL> select * from v$version; |
02 | |
03 | BANNER |
04 | ---------------------------------------------------------------- |
05 | Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi |
06 | PL/SQL Release 10.2.0.5.0 - Production |
07 | CORE 10.2.0.5.0 Production |
08 | TNS for Linux: Version 10.2.0.5.0 - Production |
09 | NLSRTL Version 10.2.0.5.0 - Production |
10 | |
11 | SQL> |
2 创建范围分区表:
01 | SQL> show user ; |
02 | USER is "SYS" |
03 | SQL> create table part_range(id number, name varchar2(30)) |
04 | 2 partition by range(id) |
05 | 3 (partition partmax values less than (maxvalue)) |
06 | 4 tablespace users; |
07 | |
08 | Table created. |
09 | |
10 | SQL> |
3 插入测试数据:
1 | SQL> insert into part_range select object_id,object_name from dba_objects |
2 | 2 where object_id<2000; |
3 | |
4 | 1953 rows created. |
5 | |
6 | SQL> |
4 在分区表part_range上创建2种分区索引:
本地分区索引【Locally partitioned index】:
1 | SQL> create index part_range_id_idx on part_range(id) local ; |
2 | |
3 | Index created. |
4 | |
5 | SQL> |
全局分区索引【Globally partitioned index】:
1 | SQL> create index part_range_name_idx on part_range( name ) tablespace users; |
2 | |
3 | Index created. |
4 | |
5 | SQL> |
5 查看分区表信息:
1 | SQL> select table_name,partition_name,tablespace_name from user_tab_partitions |
2 | 2 where table_name= 'PART_RANGE' ; |
3 | |
4 | TABLE_NAME PARTITION_NAME TABLESPACE_NAME |
5 | ------------------------------ ------------------------------ ------------------------------ |
6 | PART_RANGE PARTMAX USERS |
7 | |
8 | SQL> |
6 查看索引信息:
01 | SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions |
02 | 2 where index_name= 'PART_RANGE_ID_IDX' ; |
03 | |
04 | INDEX_NAME PARTITION_NAME TABLESPACE_NAME STATUS |
05 | ------------------------------ ------------------------------ ------------------------------ -------- |
06 | PART_RANGE_ID_IDX PARTMAX USERS USABLE |
07 | |
08 | SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes |
09 | 2 where table_name= 'PART_RANGE' ; |
10 | |
11 | INDEX_NAME TABLE_NAME TABLESPACE_NAME STATUS PAR |
12 | ------------------------------ ------------------------------ ------------------------------ -------- --- |
13 | PART_RANGE_ID_IDX PART_RANGE N/A YES |
14 | PART_RANGE_NAME_IDX PART_RANGE USERS VALID NO |
15 | |
16 | SQL> |
7 对分区表part_range执行Splitting partitions【分区分裂】操作:
1 | SQL> alter table part_range split partition partmax at (2000) |
2 | 2 into (partition p1,partition partmax); |
3 | |
4 | Table altered. |
5 | |
6 | SQL> |
注意,这里的分区分裂操作临界值是2000,即id<2000的记录将全部重组到p1分区,而partmax分区将为空,即0记录。
01 | SQL> select count (*) from part_range; |
02 | |
03 | COUNT (*) |
04 | ---------- |
05 | 1953 |
06 | |
07 | SQL> select count (*) from part_range partition(p1); |
08 | |
09 | COUNT (*) |
10 | ---------- |
11 | 1953 |
12 | |
13 | SQL> select count (*) from part_range partition(partmax); |
14 | |
15 | COUNT (*) |
16 | ---------- |
17 | 0 |
18 | |
19 | SQL> |
8 再次分别查看分区表、索引信息:
分区表:
1 | SQL> select table_name,partition_name,tablespace_name from user_tab_partitions |
2 | 2 where table_name= 'PART_RANGE' ; |
3 | |
4 | TABLE_NAME PARTITION_NAME TABLESPACE_NAME |
5 | ------------------------------ ------------------------------ ------------------------------ |
6 | PART_RANGE PARTMAX USERS |
7 | PART_RANGE P1 USERS |
8 | |
9 | SQL> |
索引信息:
01 | SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions |
02 | 2 where index_name= 'PART_RANGE_ID_IDX' ; |
03 | |
04 | INDEX_NAME PARTITION_NAME TABLESPACE_NAME STATUS |
05 | ------------------------------ ------------------------------ ------------------------------ -------- |
06 | PART_RANGE_ID_IDX P1 USERS USABLE |
07 | PART_RANGE_ID_IDX PARTMAX USERS USABLE |
08 | |
09 | SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes |
10 | 2 where table_name= 'PART_RANGE' ; |
11 | |
12 | INDEX_NAME TABLE_NAME TABLESPACE_NAME STATUS PAR |
13 | ------------------------------ ------------------------------ ------------------------------ -------- --- |
14 | PART_RANGE_ID_IDX PART_RANGE N/A YES |
15 | PART_RANGE_NAME_IDX PART_RANGE USERS VALID NO |
16 | |
17 | SQL> |
小结:对于分区分裂之后,如果包含有空分区的话,那么对于本地分区索引和全局分区索引都是可用的。这种分区分裂的方式通常也叫做快速分裂【Fast Splitting】,索引不需要rebuild。
9 如果在上述步骤7中,执行的分区分裂操作如下:
1 | SQL> alter table part_range split partition partmax at (1000) |
2 | 2 into (partition p1,partition partmax); |
3 | |
4 | Table altered. |
5 | |
6 | SQL> |
即分区分裂操作临界值是1000,id<1000的记录将重组到p1分区,id>=1000的记录将重组到partmax分区。也就是此时,分裂出来的p1和partmax这两个分区均不为空。
01 | SQL> select count (*) from part_range; |
02 | |
03 | COUNT (*) |
04 | ---------- |
05 | 1953 |
06 | |
07 | SQL> select count (*) from part_range partition(p1); |
08 | |
09 | COUNT (*) |
10 | ---------- |
11 | 953 |
12 | |
13 | SQL> select count (*) from part_range partition(partmax); |
14 | |
15 | COUNT (*) |
16 | ---------- |
17 | 1000 |
18 | |
19 | SQL> |
那么,查看到的分区表、索引信息如下:
分区表:
1 | SQL> select table_name,partition_name,tablespace_name from user_tab_partitions |
2 | 2 where table_name= 'PART_RANGE' ; |
3 | |
4 | TABLE_NAME PARTITION_NAME TABLESPACE_NAME |
5 | ------------------------------ ------------------------------ ------------------------------ |
6 | PART_RANGE PARTMAX USERS |
7 | PART_RANGE P1 USERS |
8 | |
9 | SQL> |
索引信息:
01 | SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions |
02 | 2 where index_name= 'PART_RANGE_ID_IDX' ; |
03 | |
04 | INDEX_NAME PARTITION_NAME TABLESPACE_NAME STATUS |
05 | ------------------------------ ------------------------------ ------------------------------ -------- |
06 | PART_RANGE_ID_IDX P1 USERS UNUSABLE |
07 | PART_RANGE_ID_IDX PARTMAX USERS UNUSABLE |
08 | |
09 | SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes |
10 | 2 where table_name= 'PART_RANGE' ; |
11 | |
12 | INDEX_NAME TABLE_NAME TABLESPACE_NAME STATUS PAR |
13 | ------------------------------ ------------------------------ ------------------------------ -------- --- |
14 | PART_RANGE_ID_IDX PART_RANGE N/A YES |
15 | PART_RANGE_NAME_IDX PART_RANGE USERS UNUSABLE NO |
16 | |
17 | SQL> |
小结:对于分区分裂之后,如果不包含空分区的话,那么对于本地分区索引和全局分区索引都将不可用,索引的状态都变为UNUSABLE。均需要重建:
01 | SQL> alter index PART_RANGE_ID_IDX rebuild partition p1; |
02 | |
03 | Index altered. |
04 | |
05 | SQL> alter index PART_RANGE_ID_IDX rebuild partition partmax; |
06 | |
07 | Index altered. |
08 | |
09 | SQL> alter index PART_RANGE_NAME_IDX rebuild; |
10 | |
11 | Index altered. |
12 | |
13 | SQL> |
重建之后,本地分区索引、全局分区索引信息,已由UNUSABLE变为USABLE:
01 | SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions |
02 | 2 where index_name= 'PART_RANGE_ID_IDX' ; |
03 | |
04 | INDEX_NAME PARTITION_NAME TABLESPACE_NAME STATUS |
05 | ------------------------------ ------------------------------ ------------------------------ -------- |
06 | PART_RANGE_ID_IDX P1 USERS USABLE |
07 | PART_RANGE_ID_IDX PARTMAX USERS USABLE |
08 | |
09 | SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes |
10 | 2 where table_name= 'PART_RANGE' ; |
11 | |
12 | INDEX_NAME TABLE_NAME TABLESPACE_NAME STATUS PAR |
13 | ------------------------------ ------------------------------ ------------------------------ -------- --- |
14 | PART_RANGE_ID_IDX PART_RANGE N/A YES |
15 | PART_RANGE_NAME_IDX PART_RANGE USERS VALID NO |
16 | |
17 | SQL> |
当然,如果在分裂分区的同时带上UPDATE INDEXES的话,可以在分裂分区的同时重建索引【包含本地分区索引和全局分区索引,状态均为USABLE、VALID】:
1 | SQL> alter table part_range split partition partmax at (1000) into (partition p1,partition partmax) update indexes; |
2 | |
3 | Table altered. |
4 | |
5 | SQL> |
分裂分区之后,表信息:
1 | SQL> select table_name,partition_name,tablespace_name from user_tab_partitions |
2 | 2 where table_name= 'PART_RANGE' ; |
3 | |
4 | TABLE_NAME PARTITION_NAME TABLESPACE_NAME |
5 | ------------------------------ ------------------------------ ------------------------------ |
6 | PART_RANGE PARTMAX USERS |
7 | PART_RANGE P1 USERS |
8 | |
9 | SQL> |
索引信息:
01 | SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions where index_name= 'PART_RANGE_ID_IDX' ; |
02 | |
03 | INDEX_NAME PARTITION_NAME TABLESPACE_NAME STATUS |
04 | ------------------------------ ------------------------------ ------------------------------ -------- |
05 | PART_RANGE_ID_IDX P1 USERS USABLE |
06 | PART_RANGE_ID_IDX PARTMAX USERS USABLE |
07 | |
08 | SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes where table_name= 'PART_RANGE' ; |
09 | |
10 | INDEX_NAME TABLE_NAME TABLESPACE_NAME STATUS PAR |
11 | ------------------------------ ------------------------------ ------------------------------ -------- --- |
12 | PART_RANGE_ID_IDX PART_RANGE N/A YES |
13 | PART_RANGE_NAME_IDX PART_RANGE USERS VALID NO |
14 | |
15 | SQL> |
而如果在分裂分区的同时带上UPDATE GLOBAL INDEXES的话,可以在分裂分区的同时重建全局分区索引【不包含本地分区索引,只有全局分区索引状态为VALID】,而本地分区索引需要重建:
1 | SQL> alter table part_range split partition partmax at (1000) into (partition p1,partition partmax) update global indexes; |
2 | |
3 | Table altered. |
4 | |
5 | SQL> |
分裂分区之后,表信息:
1 | SQL> select table_name,partition_name,tablespace_name from user_tab_partitions |
2 | 2 where table_name= 'PART_RANGE' ; |
3 | |
4 | TABLE_NAME PARTITION_NAME TABLESPACE_NAME |
5 | ------------------------------ ------------------------------ ------------------------------ |
6 | PART_RANGE PARTMAX USERS |
7 | PART_RANGE P1 USERS |
8 | |
9 | SQL> |
索引信息:
01 | SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions where index_name= 'PART_RANGE_ID_IDX' ; |
02 | |
03 | INDEX_NAME PARTITION_NAME TABLESPACE_NAME STATUS |
04 | ------------------------------ ------------------------------ ------------------------------ -------- |
05 | PART_RANGE_ID_IDX P1 USERS UNUSABLE |
06 | PART_RANGE_ID_IDX PARTMAX USERS UNUSABLE |
07 | |
08 | SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes where table_name= 'PART_RANGE' ; |
09 | |
10 | INDEX_NAME TABLE_NAME TABLESPACE_NAME STATUS PAR |
11 | ------------------------------ ------------------------------ ------------------------------ -------- --- |
12 | PART_RANGE_ID_IDX PART_RANGE N/A YES |
13 | PART_RANGE_NAME_IDX PART_RANGE USERS VALID NO |
14 | |
15 | SQL> |
需要注意的是,在分裂分区的同时重建索引,将会消耗更多时间来完成分裂工作,以及消耗更多的系统资源。如果系统资源较为充足的话,可以考虑带上UPDATE INDEXES选项。
10 最后,再看看另外一种比较特殊的情况。在分裂分区的时候,如果将新分区指向新的表空间【由USERS到EXAMPLE表空间】的话,并且分裂之后,包含空分区的情况。即,分裂的语句如下:
01 | SQL> alter table part_range split partition partmax at (2000) |
02 | 2 into (partition p1 tablespace example,partition partmax tablespace example); |
03 | |
04 | Table altered. |
05 | |
06 | SQL> select count (*) from part_range; |
07 | |
08 | COUNT (*) |
09 | ---------- |
10 | 1953 |
11 | |
12 | SQL> select count (*) from part_range partition(p1); |
13 | |
14 | COUNT (*) |
15 | ---------- |
16 | 1953 |
17 | |
18 | SQL> select count (*) from part_range partition(partmax); |
19 | |
20 | COUNT (*) |
21 | ---------- |
22 | 0 |
23 | |
24 | SQL> |
那么可以看到分区表:
1 | SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name= 'PART_RANGE' ; |
2 | |
3 | TABLE_NAME PARTITION_NAME TABLESPACE_NAME |
4 | ------------------------------ ------------------------------ ------------------------------ |
5 | PART_RANGE PARTMAX EXAMPLE |
6 | PART_RANGE P1 EXAMPLE |
7 | |
8 | SQL> |
索引分区信息:
01 | SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions where index_name= 'PART_RANGE_ID_IDX' ; |
02 | |
03 | INDEX_NAME PARTITION_NAME TABLESPACE_NAME STATUS |
04 | ------------------------------ ------------------------------ ------------------------------ -------- |
05 | PART_RANGE_ID_IDX P1 EXAMPLE UNUSABLE |
06 | PART_RANGE_ID_IDX PARTMAX EXAMPLE USABLE |
07 | |
08 | SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes where table_name= 'PART_RANGE' ; |
09 | |
10 | INDEX_NAME TABLE_NAME TABLESPACE_NAME STATUS PAR |
11 | ------------------------------ ------------------------------ ------------------------------ -------- --- |
12 | PART_RANGE_ID_IDX PART_RANGE N/A YES |
13 | PART_RANGE_NAME_IDX PART_RANGE USERS UNUSABLE NO |
14 | |
15 | SQL> |
小结:在分裂分区的时候,如果将新分区指向新的表空间的话,并且分裂之后,即使包含空分区的情况下,只有新空分区的本地索引不需要重建,而含有数据的新分区的本地分区索引以及全局分区索引均需重建。这有别于快速分裂,或者说是快速分裂的一种特殊情况。