分割槽索引維護（add partition）

分割槽索引維護（add partition）

總結：

1．分割槽表新增新的分割槽：

Global index: 全域性索引會被標識為“不可用”

Local index:本地索引會自動維護未被修改的分割槽，修改的分割槽可能會導致索引不可用，所以使用本地索引，新增新分割槽後也要檢測一下索引的有效性（user_ind_partitions）

2．分割槽表新增新分割槽怎麼保證索引不失效呢？

update indexes(10g以後的方法，如果是大表建議不使用，建議手動管理索引)

Oracle version

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

Create test doudou table

SQL> CREATE TABLE doudou

2 (

3 temp_date date,

4 x int,

5 y int

6 )

7 PARTITION BY RANGE (temp_date)

8 (

9 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')),

10 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')),

11 PARTITION junk VALUES LESS THAN (MAXVALUE)

12 );

Table created.

Insert data to partition table

SQL> insert into doudou select to_date('10-mar-2003')+rownum, rownum, rownum from all_users

2 where rownum <= 10;

10 rows created.

SQL> commit;

Commit complete.

Create global index

SQL> CREATE INDEX doudou_gl_idx ON doudou (temp_date)

2 GLOBAL PARTITION BY RANGE (temp_date)

3 (PARTITION doudou_13 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')),

4 PARTITION doudou_14 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')),

5 PARTITION doudou_fe VALUES LESS THAN (MAXVALUE));

Index created.

SQL> create index doudou_gl01_idx on doudou(x) global;

Index created.

Create local index

SQL> create index doudou_lo_idx on doudou(y) local;

Index created.

Check index status

SQL> select index_name, status from user_indexes where table_name = 'DOUDOU';

INDEX_NAME STATUS

------------------------------ --------

DOUDOU_LO_IDX N/A

DOUDOU_GL01_IDX VALID

DOUDOU_GL_IDX N/A

Check partition_name valid or invalid

SQL> select index_name, partition_name, status from user_ind_partitions where

2 index_name like 'DOUDOU%';

INDEX_NAME PARTITION_NAME STATUS

------------------------------ ------------------------------ --------

DOUDOU_GL_IDX DOUDOU_13 USABLE

DOUDOU_GL_IDX DOUDOU_14 USABLE

DOUDOU_GL_IDX DOUDOU_FE USABLE

DOUDOU_LO_IDX JUNK USABLE

DOUDOU_LO_IDX PART1 USABLE

DOUDOU_LO_IDX PART2 USABLE

6 rows selected.

Add partition

SQL> alter table doudou split partition junk at (to_date('15-mar-2003','dd-mon-yyyy'))

2 into ( partition part3, partition junk);

Table altered.

Check index valid or invalid

SQL> select index_name, status from user_indexes where table_name = 'DOUDOU';

INDEX_NAME STATUS

------------------------------ --------

DOUDOU_LO_IDX N/A

DOUDOU_GL01_IDX UNUSABLE

DOUDOU _GL_IDX N/A

SQL> select index_name, partition_name, status from user_ind_partitions where

2 index_name like 'DOUDOU%';

INDEX_NAME PARTITION_NAME STATUS

------------------------------ ------------------------------ --------

DOUDOU_GL_IDX DOUDOU_13 UNUSABLE

DOUDOU_GL_IDX DOUDOU_14 UNUSABLE

DOUDOU_GL_IDX DOUDOU_FE UNUSABLE

DOUDOU_LO_IDX JUNK UNUSABLE

DOUDOU_LO_IDX PART1 USABLE

DOUDOU_LO_IDX PART2 USABLE

DOUDOU_LO_IDX PART3 UNUSABLE

==>Global indexes is unusable<==

==>Local indexes of no split partition is usable or local indexes of split partition is unusable<==

Solution (rebuild index)

Global index

Global index status is unusable(user_indexes):

SQL> alter index DOUDOU_GL01_IDX rebuild;

Index altered.

Global index status is N/A(user_indexes):

SQL> select index_name, partition_name, status from user_ind_partitions where

2 index_name like 'DOUDOU_GL_IDX';

INDEX_NAME PARTITION_NAME STATUS

------------------------------ ------------------------------ --------

DOUDOU_GL_IDX DOUDOU_13 UNUSABLE

DOUDOU_GL_IDX DOUDOU_14 UNUSABLE

DOUDOU_GL_IDX DOUDOU_FE UNUSABLE

SQL> alter index DOUDOU_GL_IDX rebuild partition DOUDOU_13;

Index altered.

SQL> alter index DOUDOU_GL_IDX rebuild partition DOUDOU_14;

Index altered.

SQL> alter index DOUDOU_GL_IDX rebuild partition DOUDOU_FE;

Index altered.

SQL> select index_name, partition_name, status from user_ind_partitions where index_name like 'DOUDOU_GL_IDX';

INDEX_NAME PARTITION_NAME STATUS

------------------------------ ------------------------------ --------

DOUDOU_GL_IDX DOUDOU_13 USABLE

DOUDOU_GL_IDX DOUDOU_14 USABLE

DOUDOU_GL_IDX DOUDOU_FE USABLE

Local index

SQL> select index_name, partition_name, status from user_ind_partitions where index_name like 'DOUDOU_LO_IDX';

INDEX_NAME PARTITION_NAME STATUS

------------------------------ ------------------------------ --------

DOUDOU_LO_IDX JUNK UNUSABLE

DOUDOU_LO_IDX PART1 USABLE

DOUDOU_LO_IDX PART2 USABLE

DOUDOU_LO_IDX PART3 UNUSABLE

SQL> alter index DOUDOU_LO_IDX rebuild partition JUNK;

Index altered.

SQL> alter index DOUDOU_LO_IDX rebuild partition PART3;

Index altered.

SQL> select index_name, partition_name, status from user_ind_partitions where index_name like 'DOUDOU_LO_IDX';

INDEX_NAME PARTITION_NAME STATUS

------------------------------ ------------------------------ --------

DOUDOU_LO_IDX JUNK USABLE

DOUDOU_LO_IDX PART1 USABLE

DOUDOU_LO_IDX PART2 USABLE

DOUDOU_LO_IDX PART3 USABLE

SQL> select index_name, status from user_indexes where table_name = 'DOUDOU';

INDEX_NAME STATUS

------------------------------ --------

DOUDOU_LO_IDX N/A

DOUDOU_GL01_IDX VALID

DOUDOU_GL_IDX N/A

SQL> select index_name, partition_name, status from user_ind_partitions where

2 index_name like 'DOUDOU%';

INDEX_NAME PARTITION_NAME STATUS

------------------------------ ------------------------------ --------

DOUDOU_GL_IDX DOUDOU_13 USABLE

DOUDOU_GL_IDX DOUDOU_14 USABLE

DOUDOU_GL_IDX DOUDOU_FE USABLE

DOUDOU_LO_IDX JUNK USABLE

DOUDOU_LO_IDX PART1 USABLE

DOUDOU_LO_IDX PART2 USABLE

DOUDOU_LO_IDX PART3 USABLE

7 rows selected.

How do keep global and local index valid when add partition ?

Solution(update indexes)

SQL> alter table doudou split partition junk at (to_date('16-mar-2003','dd-mon-yyyy'))

2 into ( partition part4, partition junk) update indexes;

Table altered.

SQL> select index_name, status from user_indexes where table_name = 'DOUDOU';

INDEX_NAME STATUS

------------------------------ --------

DOUDOU_LO_IDX N/A

DOUDOU_GL01_IDX VALID

DOUDOU_GL_IDX N/A

SQL> select index_name, partition_name, status from user_ind_partitions where

2 index_name like 'DOUDOU%';

INDEX_NAME PARTITION_NAME STATUS

------------------------------ ------------------------------ --------

DOUDOU_GL_IDX DOUDOU_13 USABLE

DOUDOU_GL_IDX DOUDOU_14 USABLE

DOUDOU_GL_IDX DOUDOU_FE USABLE

DOUDOU_LO_IDX JUNK USABLE

DOUDOU_LO_IDX PART1 USABLE

DOUDOU_LO_IDX PART2 USABLE

DOUDOU_LO_IDX PART3 USABLE

DOUDOU_LO_IDX PART4 USABLE

8 rows selected.

==>”update indexes” when add partition is keeping global and local index valid<==

附表：

CREATE TABLE doudou

(

temp_date date,

x int,

y int

)

PARTITION BY RANGE (temp_date)

(

PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')),

PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')),

PARTITION junk VALUES LESS THAN (MAXVALUE)

);

CREATE INDEX doudou_gl_idx ON doudou (temp_date)

GLOBAL PARTITION BY RANGE (temp_date)

(PARTITION doudou_13 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')),

PARTITION doudou_14 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')),

PARTITION doudou_fe VALUES LESS THAN (MAXVALUE)); <==指定global index

create index doudou_gl01_idx on doudou(x) global; <==建立預設型別global index

create index doudou_lo_idx on doudou(y) local; <==建立預設型別local index

select index_name, status from user_indexes where table_name = 'DOUDOU'; <==檢視partition table的索引及狀態

select index_name, partition_name, status from user_ind_partitions where

index_name like 'DOUDOU%'; <==檢視partition index是否有效

參考文獻：

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3296803815605

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-774568/，如需轉載，請註明出處，否則將追究法律責任。

分割槽索引維護（add partition）

相關文章