Oracle數據庫：全局索引的ONLINE重建要影響執行速度

作者：佚名 2011-07-04 10:19:41

在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.

可以看到，如果要ONLINE重建這個索引，將會對表T執行32全表掃描。如果要對比較大的表進行在線重建索引，全局索引的重建代價是比較高的，因此耗時會比較長。

【編輯推薦】

解析OCR與Voting Disk以及其管理
Oracle認證：Oracle優化器的優化方式
Oracle數據庫使用OMF來簡化數據文件的管理
淺談禁用以操作系統認證方式登錄Oracle數據庫
淺析Windows通過ODBC訪問linux的Oracle數據庫

責任編輯：趙鵬來源：中國IT實驗室

索引 ONLINE