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

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全表扫描。如果要对比较大的表进行在线重建索引，全局索引的重建代价是比较高的，因此耗时会比较长。