ITPub博客

首页 > 数据库 > Oracle > 分区索引维护(add partition)

分区索引维护(add partition)

原创 Oracle 作者:lovehewenyu 时间:2013-10-17 17:14:42 0 删除 编辑

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

上一篇: 优化sql的利器SQLT
请登录后发表评论 登录
全部评论
Oracle,Mysql,EMC 存储,NBU备份

注册时间:2012-02-03

  • 博文量
    267
  • 访问量
    1295601