分区索引重建

来源：互联网发布：淘宝企业店铺换公司编辑：程序博客网时间：2024/06/02 08:12

创建分区表：
CREATE TABLE test(
      id NUMBER,
      name VARCHAR2(10)
)
partition by range(id)(
          partition p1 values less than (1000),
          partition p2 values less than (2000),
          partition p3 values less than (maxvalue)
);

创建分区索引
LOCAL索引结构
CREATE INDEX id_local ON test(id) LOCAL;

在ID列上创建一个LOCAL的索引
create index id_local on test(id) local;
重新在name列上创建一个GLOBAL的索引
create index ame_global on test(name) global;
insert into test values(999,'p1');
insert into test values(1999,'p2');
insert into test values(2999,'p3');

SQL> select * from test;

        ID NAME
---------- ----------
       999 p1
      1999 p2
      2999 p3

查询当前用户下有哪些是分区表：
SQL> SELECT table_name, partitioning_type,partition_count FROM USER_PART_TABLES;

TABLE_NAME PARTITION PARTITION_COUNT
------------------------------ --------- ---------------
TEST RANGE 3

查询当前用户下有哪些分区索引：
SQL> SELECT index_name,table_name FROM USER_PART_INDEXES;

INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
ID_LOCAL TEST

索引对应的分区以及索引的状态
SQL> select index_name,partition_name,status from user_ind_partitions;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
ID_LOCAL                       P2                             USABLE
ID_LOCAL                       P1                             USABLE
ID_LOCAL                       P3                             USABLE
移动分区表使索引失效
SQL> alter table test move partition p1 tablespace ww;

表已更改。

SQL> alter table test move partition p2 tablespace ww;

表已更改。

SQL> alter table test move partition p3 tablespace ww;

表已更改。

本地分区失效
SQL> select index_name,partition_name,status from user_ind_partitions;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
ID_LOCAL                       P3                             UNUSABLE
ID_LOCAL                       P2                             UNUSABLE
ID_LOCAL                       P1                             UNUSABLE

Rebuilding Local Index Partitions

Rebuild local indexes using either ALTER INDEX or ALTER TABLE as follows:

ALTER INDEX ... REBUILD PARTITION/SUBPARTITION

This statement rebuilds an index partition or subpartition unconditionally.

ALTER TABLE ... MODIFY PARTITION/SUBPARTITION ... REBUILD UNUSABLE LOCAL INDEXES

This statement finds all of the unusable indexes for the given table partition or subpartition and rebuilds them. It only rebuilds an index partition if it has been marked UNUSABLE.

SQL> alter index id_local rebuild partition p1;

索引已更改。

SQL> select index_name,partition_name,status from user_ind_partitions;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
ID_LOCAL                       P1                             USABLE
ID_LOCAL                       P3                             UNUSABLE
ID_LOCAL                       P2                             UNUSABLE

SQL>

SQL> ALTER TABLE test MODIFY PARTITION p2 REBUILD UNUSABLE LOCAL INDEXES;

表已更改。

SQL> select index_name,partition_name,status from user_ind_partitions;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
ID_LOCAL                       P1                             USABLE
ID_LOCAL                       P2                             USABLE
ID_LOCAL                       P3                             UNUSABLE

SQL>

全局索引的状态:
SQL> select index_name,table_name,status from user_indexes where index_name='NAM
_GLOBAL';

INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
NAM_GLOBAL TEST UNUSABLE

SQL>

You can rebuild global index partitions in two ways:

++ Rebuild each partition by issuing the ALTER INDEX ... REBUILD PARTITION statement (you can run the rebuilds concurrently).

++ Drop the entire global index and re-create it. This method is more efficient because the table is scanned only one time.

SQL> ALTER INDEX nam_global REBUILD;

索引已更改。

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

INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
NAM_GLOBAL TEST VALID

SQL>

删除分区也会导致全局分区索引失效
SQL> ALTER TABLE test TRUNCATE partition p1;

表被截断。

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

INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
NAM_GLOBAL TEST UNUSABLE

SQL> select index_name,partition_name,status from user_ind_partitions;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
ID_LOCAL                       P1                             USABLE
ID_LOCAL                       P2                             USABLE

0 0