Oracle splitting partitions简单小结

            本文简单记录在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> 

即分区分裂操作临界值是1000id<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> 

小结:在分裂分区的时候,如果将新分区指向新的表空间的话,并且分裂之后,即使包含空分区的情况下,只有新空分区的本地索引不需要重建,而含有数据的新分区的本地分区索引以及全局分区索引均需重建。这有别于快速分裂,或者说是快速分裂的一种特殊情况

本文部分内容参考范大师