關於索引前面已經寫了幾篇博文，但寫著寫著發現依然有好多內容，不禁感嘆Oracle的博大精深，就一個小小的概念，牽扯到的知識點以及內容何其多。本文就結合以前碰到的一個案例來談一談索引重建話題，如有錯誤，歡迎各位專家批評指正。
一：什麼情況下要重建索引
索引是否需要定期重建一直是一個爭論不休的話題。
部分DBA認為若基表頻繁的增、刪、改會造成大量索引碎片，重建後會改善索引效能。對於此觀點翻閱了大量資料並沒找到權威的說法，倒是在tom的《Export one on Oracle》找到了相反的觀點：即認為索引會自我平衡和自我管理，即便基表經過頻繁的增刪改操作形成碎片，這些碎片在後續也可以被重用。

為證明tom的觀點，在測試環境做了相關實驗證明碎片確實可以重用，因此索引一般不會有碎片，基表頻繁的增刪改也不能成為重建索引的理由。那麼究竟什麼情況下要重建索引呢？
1.索引或索引分割槽因為介質故障損壞，例如索引所在表空間資料檔案被誤刪除。
2.索引試圖中STATUS列標記為UNUSABLE
3.索引對應基表move到新表空間
4.索引計劃遷移到新表空間
5.修改索引的某些儲存引數
二：重建索引的方法
1.重新建立
drop index index_name;
create index index_name;
2.alter index index_name rebuild;
3.alter index index_name rebuild online;
三：重建索引注意事項
此部分是本文的重點，將對上述的3中方法透過實驗進行比較和說明，以及每種方法的利與弊。
為方便測試，建立了一整包含1000W左右的測試表，大小約為400M,建立完成後對錶ID建立了索引，具體資訊如下：

點選(此處)摺疊或開啟

desc t
Name Null? Type
-----------------------
ID NOT NULL NUMBER
DATA VARCHAR2(100)
SELECT count(*) FROM t;
COUNT(*)
----------
11030008
SELECT sum(BLOCKS)*8/1024 from user_extents t where t.segment_name='T';
SUM(BLOCKS)*8/1024
------------------
400
SELECT index_name,tablespace_name from user_indexes t where t.table_name='T';
INDEX_NAME TABLESPACE_NAME
------------------------------
IDX_T USERS

因後面的測試會觀察表和索引所在的表空間使用情況，此處先貼上一下表空間的情況

1.重新建立(drop-->create index)
SQL> drop index IDX_T;
  Elapsed: 00:00:00.41
SQL> create index idx_t on t(id);
  Index created.
Elapsed: 00:01:45.08
  共計用時146S，同時在create index語句執行過程中會話一直持有TM鎖 Imode=4，阻塞了其他會話增刪改等DML語句，直到索引建立完成。

2.alter index index_name rebuild;
先看看官方關於rebuild的說明：

官方很明確的說明了rebuild的原理，以原有索引作為基礎進行重新建立（可以透過10046確認），且效能要好於drop然後重新create步驟，下面進行測試：
rebuild要持有相關鎖，若表有未提交的事務，會報錯，因此rebuild前請保證沒有相關事務。

SQL> alter index idx_t rebuild;
Index altered.
Elapsed: 00:00:53.76

共計用時53S，相比上一種方法要快，像drop->create方法一樣，rebuild語句執行過程會話一直持有TM鎖Imode=4，阻塞其他會話增刪改等DML語句，直到索引rebuild完成。相關鎖截圖如下：

rebuild還有一個值得關注的地方就是空間問題，Oracle在建立新索引過程中，並不會刪除舊索引，直至新索引rebuild成功。那麼說明
1)rebuild過程，若查詢操作能用到舊索引依然會用舊索引，查詢效率不會因rebuild而降低。以下是索引rebuild過程中語句執行的情況，可以清楚的看到執行計劃用到了索引。

2)rebuild方式建立索引過程需要額外的空閒空間，額外空間的大小基本為舊索引大小，rebuild成功後，rebuild過程佔用的額外空間將會被釋放。以下是rebuild過程對錶空間使用情況的一部分截圖：

3.alter index index_name rebuild online;
同樣看一下官方文件：

rebuild online執行過程中基表可以進行增刪改等DML操作，線上rebuild可以保證了業務連續性，比rebuild要高階一些。
alter index idx_t rebuild online;
Index altered.
Elapsed: 00:00:58.92
共計用時58S，和第二種方法差不多。和rebuild一樣，rebuild online索引重建過程也需要額外的空閒空間，另外rebuild online過程中舊索引依然可用，限於篇幅不再進行截圖說明。
和rebuild不同的一點rebuild online以基表作為基礎進行重新建立（可以透過10046確認），因此相對rebuild會慢一些。
rebuild online怎麼保證重建過程不影響基表的DML操作呢？奧秘在於在rebuild online過程中，oracle會自動維護一日誌(journal)表,透過鎖以及dba_objects檢視的相關資訊可以跟蹤到該表。

由於要額外維護一張日誌表，那麼如果在rebuild online過程中，基表發生了大量的增刪改操作，整個rebuild online重建索引過程就會異常的慢。
另外還有一個特別重要的說明一下：雖然rebuild index online在執行期間不會阻塞DML操作，但在開始和結束階段，需要請求模式為4的TM鎖。如果在rebuild index online開始前或結束時，有其它長時間的事務在執行會阻塞
rebuild index online，透過以下實驗說明：
會話一（35）：

會話二（55）：

透過TOM寫的查詢語句可以看到

在會話一中commit再次用上述語句檢查發現已經沒有阻塞了，等待一段時間rebuild online成功完成。

總結：
1.drop-->create index,alter index rebulid會阻塞基表的增刪改DML操作，alter index rebuild online不會阻塞，保證線上重新建立。
2.alter index rebulid以及rebuild online重建過程要求有額外的空閒空間，drop-->create index無此要求。
3.一般情況下rebuild速度最快，針對rebuild online請保證重建開始前與結束前無相關事務，否則會rebuild online會一直處於等待狀態。
上述三種重建索引方法各有利弊，在實際生產環境中具體採用哪種方法請結合具體情況酌情處理。
另外關於rebuild和rebuild online的區別，metalink上有一篇文章，請大家參考。
metalink Note:272762.1

Problem:
========
- Online Index rebuild takes a long time.
- ONLINE INDEX REBUILD SCANS THE BASE TABLE AND NOT THE INDEX

Symptoms:
=========
Performance issues while rebuilding very large indexes.
- The offline rebuilds of their index is relatively quick -finishes in 15 minutes.
- Issuing index rebuild ONLINE statement => finishes in about an hour.
- This behavior of ONLINE index rebuilds makes it a non-option for large tables
as it just takes too long to scan the table to rebuild the index. The
offline may not be feasible due to due to the 24/7 nature of the database.
- This may be a loss of functionality for such situations.
- If we attempt to simultaneously ONLINE rebuild the same indexes we may encounter
hanging behavior indefinitely (or more than 6 hours).

DIAGNOSTIC ANALYSIS:
--------------------
We can trace the sessions rebuilding the indexes with 10046 level 12.
Comparing the IO reads for the index-rebuild and the index-rebuild-online
reveals the following:

-ONLINE index rebuilds
It scans the base table and it doesn't scan the blocks of the index.

-OFFLINE index rebuilds
It scans the index for the build operation.

- This behaviour is across all versions.

Cause
Cause/Explanation
=============
When you rebuild index online,
- it will do a full tablescan on the base table.
- At the same time it will maintain a journal table for DML data, which has
changed during this index rebuilding operation.
So it should take longer time, specially if you do lots of DML on the same table,
while rebuilding index online.

On the other hand, while rebuilding the index without online option, will grab
the index in X-mode and rebuild a new index segment by selecting the data from
the old index. So here we are
- not allowing any DML on the table hence there is no journal table involved
- and it is doing an index scan
Hence it will be pretty fast.
Fix
Solution/Conclusion:
===========
- The ONLINE index rebuild reads the base table, and this is by design.
- Rebuilding index ONLINE is pretty slow.
- Rebuilding index offline is very fast, but it prevents any DML on the base table.

淺談索引系列之索引重建

相關文章