Oracle效能分析8：建立索引

標籤：database   oracle   效能   資料庫   

在建立索引時，我們往往希望能夠預估索引大小，以評估對現有工程環境的影響，我們也希望建立索引的過程能夠最小化的影響我們正在啟動並執行工程環境，並能查看索引的狀況。

預估索引大小

預估索引大小，最好的辦法是在測試環境中建立它，測試環境最好包含完整的工程環境資料，否則只有通過部分資料來推算完整的索引大小。
如果不能搭建測試環境，Oracle提供了預存程序DBMS_SPACE.CREATE_INDEX_COST來估算索引的大小，下面是一個例子：

declare  used_bytes  number(10);  alloc_bytes number(10);begin  dbms_stats.gather_table_stats(user, 'HISTORYALARM');  dbms_space.create_index_cost('create index idx_historyalarm on historyalarm(position1)',                               used_bytes      => used_bytes,                               alloc_bytes     => alloc_bytes);  dbms_output.put_line('used_bytes : ' || used_bytes);  dbms_output.put_line('alloc_bytes : ' || alloc_bytes);end;

在計算索引大小的時候，你需要先收集表的統計資訊，因為Oracle是根據表的資料資訊來推算的，下面是輸出的結果：

used_bytes : 151994511alloc_bytes : 251658240

建立索引

在估算了索引大小後，如果沒有問題，就可以開始實際的建立索引了：

create index idx_historyalarm on historyalarm(position1) tablespace uep4x_fm_index

建立索引的索引會對錶加排他DDL鎖（Exclusive DDL lock），這會防止其他會話得到他們自己的DDL鎖或TM（DML）鎖，也就是在建立索引期間你能夠查詢一個表，但是無法以任何方式修改這個表。這導致建立索引的操作在工程環境中實施時會存在問題，因此，Oracle企業版提供了線上建立索引的方式：

create index idx_historyalarm on historyalarm(position1) tablespace uep4x_fm_index online

online將改變具體建立索引的過程，Oracle不會再加一個排他DDL鎖來防止資料修改，改為試圖得到表上的一個低級（mode 2）TM鎖，這將有效地防止其他DDL（data definition language，包括CREATE、ALTER、DROP等）操作發生，但允許DML（data manipulation language，包括SELECT、UPDATE、INSERT、DELETE）操作正常進行。在Oracle的內部，Oracle會將建立索引期間做的DML操作放到一個暫存資料表中，等建立索引操作完成後，再將DML操作所做的修改同步到新的索引。這樣就有效解決了工程環境中實施的問題。

查看索引資訊

可以通過上面的方式查看建立成功後的索引：

select * from user_indexes where index_name = upper('idx_historyalarm')

可以通過下面的方式顯示所佔用的空間的實際數額：

select bytes from user_segments where segment_name = upper('idx_historyalarm')

下面是輸出結果，空間分配位元組數的估計量略小於實際使用量：

BYTES--------------------------293601280

隨著記錄插入到表中，該索引將增加，對索引大小監控可以確保有足夠的磁碟空間，以適應未來的資料增加需求。

Oracle效能分析8：建立索引