分区索引重建

超、凢脫俗 2022-08-24 11:56 240阅读 0赞

创建分区表：  
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';

SQL> select index\_name,partition\_name,status  from user\_ind\_partitions;

INDEX\_NAME                     PARTITION\_NAME                 STATUS  
\------------------------------ ------------------------------ --------  
ID\_LOCAL                       P1                             USABLE  
ID\_LOCAL                       P2                             USABLE