Oracle数据库重建一个表的索引的时候，基本都是全局索引而且都是ONLINE方式重建，每个分区的重建时间基本相同，大约在23分钟左右。其实导致问题的原因很简单：由于采用ONLINE方式，而且全局索引的每一个分区的数据可能来自这个表的任何一个分区，所以ORACLE对于全局索引的任何一个分区的重建都要走全表扫描。以下是这一过程的代码演示部分：
1.查看当前用户，并以当前用户创建表T
- 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.
2.为表T创建全局索引
- SQL> CREATE INDEX T_ID_IDX ON T(ID) GLOBAL
- 2 PARTITION BY HASH(ID)
- 3 PARTITIONS 32
- 4 /
- Index created.
3.查询索引列名称和分区列名称，并以指定的格式显示
- 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
- SQL> INSERT INTO T SELECT OBJECT_ID,OBJECT_NAME FROM ALL_OBJECTS;
- 50617 rows created.
- SQL> COMMIT;
- Commit complete.
#p#
4.DBMS_STATS.GATHER_TABLE_STATS统计表,列,索引的统计信息.
- 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全表扫描。如果要对比较大的表进行在线重建索引，全局索引的重建代价是比较高的，因此耗时会比较长。
【编辑推荐】
