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