Oracle数据库：全局索引的ONLINE重建要影响执行速度

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.