有一個應用,需要建立索引,建立索引一般有兩種方法,一種是
一種是
字面意思上看,一個是線上,一個是非線上,有什麼不同?
1.語句執行時間的不同
建立測試表,
使用非線上建立索引,用時00.06秒,
使用線上方式建立索引,用時00.32秒,
表隻有一條資料,ONLINE是非ONLINE用時的5倍以上了。
2.阻塞對象的不同
非線上方式建立索引期間,執行任何DML語句,會hang住,直至索引建立完,
出現hang是現象,原理則是鎖等待。
從V$LOCKED_OBJECT視圖可以了解鎖等待資訊,使用DBA_OBJECTS視圖可以知道,OBJECT_ID是18和168111代表的對象是什麼,
線上方式建立索引期間,允許任何DML語句的執行,不會阻塞。但實際從V$LOCKED_OBJECT看,是有一些鎖等待資訊的,
167111知道是TBL_INDEX表,我們看下168114(此處截圖問題請忽略,預設168141就是168114),他代表的對象是SYS_JOURNAL_168113,檢視168113代表的對象則是我們建立TBL_INDEX表的索引IDX_TBL_INDEX_01,
我們看下SYS_JOURNAL_168113,他是一張表,
表有四個字段,
記錄為空(此處截圖問題請忽略,預設168112是168114),
表大小為0,
3.執行邏輯的不同
我們對這兩種方法執行10046,看下Oracle執行了什麼,
(1) 非線上方式的trace主要内容,
首先,我們看見了以SHARE NOWAIT模式LOCK了TBL_INDEX整張表,
向obj$、seg$、icol$、ind$這些資料字典中維護索引相關資訊,
完成非唯一索引的建立,
(2) 線上方式的trace主要内容,
首先,以ROW SHARE模式LOCK表TBL_INDEX,這是和非線上方式一點不同,
另外的不同,就是會建立一張叫”SYS_JOURNAL_92450”的表,索引建立用的是這張“臨時表”,是以不會直接影響原表的DML語句,
這張表用完了,會被drop purge,是以資源回收筒找不着痕迹,
删除一些con$、seg$資料字典的記錄,
我們從這兩種建立索引生成的trace檔案大小也可以得出一些結論,online方式建立索引的trace檔案大小是非online方式建立索引的trace檔案大小的10倍,說明online方式建立索引要執行更多的工作,盡管不會影響原表的DML語句,是以用時要久一些,
總結:
(1) online和非online方式建立索引,效果相同。
(2) online方式建立索引,由于使用了一張臨時表,以ROW SHARE鎖表,不會阻塞原表DML的語句,非online方式建立索引,則會以SHARE NOWAIT鎖表,阻塞原表DML語句。
(3) 由于online方式建立索引,Oracle執行工作複雜,是以比非online方式建立索引用時要久。
(4) 一句話“不能什麼便宜均占着”,要麼選擇可以快速建立索引的非online方式但建立期間會鎖表阻塞DML語句,要麼選擇不會阻塞原表DML語句的online方式建立索引但用時較久。從實際來看,我了解,若小表選擇任何一種均可,大表,尤其是生産系統,找不着非高峰時間,選擇online更合理一些,若不關注是否影響DML操作,則兩種方式均可以了。