Oracle splitting partitions简单小结[转]

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>

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