oracle 效能最佳化

索引的說明

索引是與表相關的一個可選結構，在邏輯上和物理上都獨立於表的資料，索引能最佳化查詢，不能最佳化DML操作，Oracle自動維護索引，頻繁的DML操作反而會引起大量的索引維護。

通常，為檢索表資料，資料庫以交替方式先讀取索引塊，然後讀取相應的表塊。

1 大表，返回的行數<5%

2 經常使用where子句查詢的列

3 離散度高的列

4 更新鍵值代價低

5 邏輯AND、OR效率高

6 檢視索引在建在那表、列：

   select * from user_indexes;

   select * from user_ind_columns;

oracle  索引結構：

索引結構

oracle索引分為兩大類結構：

B樹索引結構<balance>

類似於字典查詢，最後到leaf block ，存的是資料rowid和資料項

1.葉塊之間使用雙向鏈連線，為了可以範圍查詢。

2.刪除錶行時，索引葉塊也會更新，但只是邏輯更改，並不做物理的刪除葉塊。

3.索引葉塊不儲存錶行鍵值null的資訊。

點陣圖索引結構<bitmap>

在oracle中是根據rowid來定位記錄的，因此，我們需要引入start rowid和end rowid，透過start rowid ，end rowid 和二進位制位的偏移，我們就可以非常快速的計算出二進位制位所代表的表記錄rowid。點陣圖索引的最終邏輯結構如下圖：

我們稱每一單元的<key ,startrowid,end rowid,bitmap>為一個點陣圖片段。當我們修改某一行資料的時候，我們需要鎖定該行列值所對應的點陣圖片段，如果我們進行的是更新操作，同時還會鎖定更新後新值所在的點陣圖片段。例如我們將列值從01修改為03，就需要同時鎖定01和03點陣圖片段，此時如果有其他使用者需要修改與01或者03關聯的表記錄上的索引欄位，就會被阻塞，因此點陣圖索引不適合併發環境，在併發環境下可能會造成大量事務的阻塞。

建立索引的方式：

1.唯一索引：鍵值不重複

create unique index doctor_index on t_doctor(empno)

drop index doctor_index

2.一般索引：鍵值可重複

create index doctor_index on t_doctor(empno)

drop index doctor_index

3.複合索引：繫結了多個列

create index doctor_index on t_doctor(empno，job)

drop index doctor_index

4.反向索引：為避免平衡樹索引熱塊，如t_doctor表中empno開頭都是“7”，這樣構建索引樹的時候，很可能會把所有資料分配到一個塊裡，使用反向索引，避免此類問題，使索引樹分佈均勻

create index doctor_index on t_doctor(empno) reverse

drop index doctor_index

5.函式索引：查詢時必須用到這個函式，才會使用到

create index func_index on t_doctor(lower(empno))

--select * from t_doctor where lower(empno) = 'lina'

drop index func_index 

6.壓縮索引：不常用

create index doctor_index on t_doctor(empno) compress

drop index doctor_index

7.升序降序索引：

create index doctor_index on t_doctor(empno desc, job asc)

drop index doctor_index

索引碎片問題

由於對基表做DML操作， 導致索引表塊的自動更改操作，尤其是基表的delete操作會引起index表的index_entries的邏輯刪除，注意只有當一個索引塊中的全部index_entry都被刪除了，才會把這個索引塊刪除，索引對基表的delete、insert操作都會產生索引碎片問題。

在Oracle文件裡並沒有清晰的給出索引碎片的量化標準,Oracle建議透過Segment Advisor(段顧問）解決表和索引的碎片問題，如果你想自行解決，可以透過檢視index_stats檢視，當以下三種情形之一發生時，說明積累的碎片應該整理了（僅供參考）。

  檢視執行計劃：set autotrace traceonly explain;

analyze index ind_1 validate structure;

select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats;

1.HEIGHT >=4   

 

2 PCT_USED< 50%   

 

3 DEL_LF_ROWS/LF_ROWS>0.2

alter index ind_1 rebuild [online] [tablespace name];