索引重建的資料來源

以前一直認為，索引重建的資料來源就是索引本身，直到一次同事的測試時發現了這個問題，才糾正了我這個錯誤。

 

 

一般情況下，索引的重建仍然會嘗試讀取索引：

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;

表已建立。

SQL> CREATE INDEX IND_T_OBJECT_NAME ON T(OBJECT_NAME);

索引已建立。

SQL> EXPLAIN PLAN FOR ALTER INDEX IND_T_OBJECT_NAME REBUILD;

已解釋。

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 1721975976

--------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT  |                   | 58483 |  3769K|   161   (2)| 00:00:02 |
|   1 |  INDEX BUILD NON UNIQUE| IND_T_OBJECT_NAME |       |       |            |          |
|   2 |   SORT CREATE INDEX    |                   | 58483 |  3769K|            |          |
|   3 |    INDEX FAST FULL SCAN| IND_T_OBJECT_NAME |       |       |            |          |
--------------------------------------------------------------------------------------------

已選擇10行。

除非在重建索引時指定ONLINE引數：

SQL> EXPLAIN PLAN FOR ALTER INDEX IND_T_OBJECT_NAME REBUILD ONLINE;

已解釋。

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2895142991

--------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT  |                   | 58483 |  3769K|   161   (2)| 00:00:02 |
|   1 |  INDEX BUILD NON UNIQUE| IND_T_OBJECT_NAME |       |       |            |          |
|   2 |   SORT CREATE INDEX    |                   | 58483 |  3769K|            |          |
|   3 |    TABLE ACCESS FULL   | T                 | 58483 |  3769K|   161   (2)| 00:00:02 |
--------------------------------------------------------------------------------------------

已選擇10行。

ONLINE模式採用讀取表的方式是為了減少REBUILD索引時的鎖表時間。

而透過同事的例子發現，Oracle選擇索引並不是固定的，而是經過CBO判斷後，認為索引掃描的效率更高。

SQL> CREATE TABLE T (ID NUMBER);

表已建立。

SQL> INSERT INTO T SELECT ROWNUM FROM DBA_OBJECTS;

已建立50633行。

SQL> COMMIT;

提交完成。

SQL> CREATE INDEX IND_T_ID ON T(ID);

索引已建立。

SQL> EXPLAIN PLAN FOR ALTER INDEX IND_T_ID REBUILD;

已解釋。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', CASCADE => TRUE)

PL/SQL 過程已成功完成。

SQL> EXPLAIN PLAN FOR ALTER INDEX IND_T_ID REBUILD;

已解釋。

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 3865827442

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT  |          | 50633 |   197K|    20   (5)| 00:00:01 |
|   1 |  INDEX BUILD NON UNIQUE| IND_T_ID |       |       |            |          |
|   2 |   SORT CREATE INDEX    |          | 50633 |   197K|            |          |
|   3 |    TABLE ACCESS FULL   | T        | 50633 |   197K|    20   (5)| 00:00:01 |
-----------------------------------------------------------------------------------

已選擇10行。

看上面這個例子，由於表中只有一列，Oracle在重建索引的時候認識到讀取表的代價要比讀取索引的代價低，因此選擇了全表掃描作為索引重建的資料來源。

看來不僅是DML採用CBO最佳化模式，就是DDL也會根據代價的不同而調整執行計劃。