全局索引的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
T_ID_IDX             SYS_P243
T_ID_IDX             SYS_P244
T_ID_IDX             SYS_P245
T_ID_IDX             SYS_P246
T_ID_IDX             SYS_P247
T_ID_IDX             SYS_P248
T_ID_IDX             SYS_P249
T_ID_IDX             SYS_P250
T_ID_IDX             SYS_P251
T_ID_IDX             SYS_P252
T_ID_IDX             SYS_P253
T_ID_IDX             SYS_P254
T_ID_IDX             SYS_P255
T_ID_IDX             SYS_P256

 

32 rows selected.

 

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
----------------------------------------------------------------------------------------------------
Plan hash value: 78911014

-----------------------------------------------------------------------------------
| 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 RANGE ALL |          | 50617 |   247K|    56 |     1 |     6 |
|*  4 |     TABLE ACCESS FULL  | T        | 50617 |   247K|    56 |     1 |     6 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - filter(TBL$OR$IDX$PART$NUM("TEST"."T",58596,0,1048576,"ID")=1)

 

Note
-----
   - cpu costing is off (consider enabling it)

20 rows selected.

 

SQL> EXPLAIN PLAN FOR ALTER INDEX T_ID_IDX REBUILD PARTITION SYS_P226;

 

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|          |       |       |       |     2 |     2 |
|   4 |     INDEX FAST FULL SCAN| T_ID_IDX |       |       |       |     2 |     2 |
------------------------------------------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)

 

15 rows selected.

 

SQL> EXPLAIN PLAN FOR ALTER INDEX T_ID_IDX REBUILD PARTITION SYS_P226 ONLINE;

 

Explained.

 

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 78911014

-----------------------------------------------------------------------------------
| 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 RANGE ALL |          | 50617 |   247K|    56 |     1 |     6 |
|*  4 |     TABLE ACCESS FULL  | T        | 50617 |   247K|    56 |     1 |     6 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

 

   4 - filter(TBL$OR$IDX$PART$NUM("TEST"."T",58596,0,1048576,"ID")=2)

Note
-----
   - cpu costing is off (consider enabling it)

 

20 rows selected.

可以看到，如果要ONLINE重建这个索引，将会对表T执行32全表扫描。如果要对比较大的表进行在线重建索引，全局索引的重建代价是比较高的。