分区索引维护（add partition）

最新推荐文章于 2023-03-21 23:36:28 发布

转载最新推荐文章于 2023-03-21 23:36:28 发布 · 571 阅读

标签

#数据库

分区索引维护（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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26442936/viewspace-774568/