Oracle数据库：全局索引的ONLINE重建要注意影响(一)

前几天遇到一个问题，重建一个表的索引的时候，竟然用了8个多小时。后来仔细检查了一下创建慢的索引，发现基本都是全局索引而且都是 ONLINE方式重建，每个分区的重建时间基本相同，大约在23分钟左右。其实导致问题的原因很简单：由于采用ONLINE方式，而且全局索引的每一个分区的数据可能来自这个表的任何一个分区，所以ORACLE对于全局索引的任何一个分区的重建都要走全表扫描。
　　SQL> SHOW USER
　　USER is "TEST"
　　SQL> CREATE TABLE T(ID INT,NAME VARCHAR2(30))
　　2 PARTITION BY RANGE(ID)
　　3 (
　　4 PARTITION P1 VALUES LESS THAN(10000),
　　5 PARTITION P2 VALUES LESS THAN(20000),
　　6 PARTITION P3 VALUES LESS THAN(30000),
　　7 PARTITION P4 VALUES LESS THAN(40000),
　　8 PARTITION P5 VALUES LESS THAN(50000),
　　9 PARTITION PMAX VALUES LESS THAN(MAXVALUE)
　　10 )
　　11 /
　　Table created.
　　SQL> CREATE INDEX T_ID_IDX ON T(ID) GLOBAL
　　2 PARTITION BY HASH(ID)
　　3 PARTITIONS 32
　　4 /
　　Index created.
　　SQL> COL INDEX_NAME FORMAT A20
　　SQL> COL PARTITION_NAME FORMAT A20
　　SQL> SELECT INDEX_NAME,PARTITION_NAME FROM USER_IND_PARTITIONS WHERE INDEX_NAME=T_ID_IDX;
　　INDEX_NAME PARTITION_NAME
　　-------------------- --------------------
　　T_ID_IDX SYS_P225
　　T_ID_IDX SYS_P226
　　T_ID_IDX SYS_P227
　　T_ID_IDX SYS_P228
　　T_ID_IDX SYS_P229
　　T_ID_IDX SYS_P230
　　T_ID_IDX SYS_P231
　　T_ID_IDX SYS_P232
　　T_ID_IDX SYS_P233
　　T_ID_IDX SYS_P234
　　T_ID_IDX SYS_P235
　　T_ID_IDX SYS_P236
　　T_ID_IDX SYS_P237
　　T_ID_IDX SYS_P238
　　T_ID_IDX SYS_P239
　　T_ID_IDX SYS_P240
　　T_ID_IDX SYS_P241
　　T_ID_IDX SYS_P242

SQL> INSERT INTO T SELECT OBJECT_ID,OBJECT_NAME FROM ALL_OBJECTS;
　　50617 rows created.
　　SQL> COMMIT;
　　Commit complete.
　　SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,T,CASCADE=>TRUE);
　　PL/SQL procedure successfully completed.
　　SQL> EXPLAIN PLAN FOR ALTER INDEX T_ID_IDX REBUILD PARTITION SYS_P225;
　　Explained.
　　SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
　　PLAN_TABLE_OUTPUT
　　Plan hash value: 2508449852
　　------------------------------------------------------------------------------------
　　| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
　　------------------------------------------------------------------------------------
　　| 0 | ALTER INDEX STATEMENT | | 50617 | 247K| 56 | | |
　　| 1 | INDEX BUILD NON UNIQUE | T_ID_IDX | | | | | |
　　| 2 | SORT CREATE INDEX | | 50617 | 247K| | | |
　　| 3 | PARTITION HASH SINGLE| | | | | 1 | 1 |
　　| 4 | INDEX FAST FULL SCAN| T_ID_IDX | | | | 1 | 1 |
　　------------------------------------------------------------------------------------
　　Note
　　-----
　　- cpu costing is off (consider enabling it)
　　15 rows selected.
　　SQL> EXPLAIN PLAN FOR ALTER INDEX T_ID_IDX REBUILD PARTITION SYS_P225 ONLINE;
　　Explained.
　　SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
　　PLAN_TABLE_OUTPUT
　　-----------------------------------------------------------