Oracle性能分析7：創建索引

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

預估索引大小

預估索引大小，最好的辦法是在測試環境中創建它，測試環境最好包含完整的工程環境數據，否則只有通過部分數據來推算完整的索引大小。
如果不能搭建測試環境，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 : 151994511
alloc_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

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