Oracle index 使用的一個總結

1. 什麼情況需要建index
oracle中的很多表建索引是根據業務需求建的，比如：主鍵，常用的columns。 oracle提出了一個建index 的理論依據就是可選擇性(selectivity)：

比較一下列中唯一數量的個數和表中的行數，就可以判斷該列的可選擇性。
如果該列的”唯一鍵的數量/表中的行數”的比值越接近1，則該列的可選擇性越高，該列就越適合建立索引，同樣索引的可選擇性也越高。
在可選擇性高的列上進行查詢時，返回的資料就較少，比較適合使用索引查詢。

2. 資料的理想路徑  ROWID--> 資料內容；
一般的是  index-->ROWID---> 資料內容；

資料內容如果跨多個DB Block，就是行連線
rowid指的不是資料內容，而是一個指標，就是行遷移。  如果多了的話，需要處理碎片。

3. 索引的掃描分類

索引唯一掃描(index unique scan)   --- where 條件之後，返回一行記錄
索引範圍掃描(index range scan)    --- 多行記錄
索引全掃描(index full scan)       --- 排序
索引快速掃描(index fast full scan) -- 不排序

4. oracle 認為，選擇的結果集超過全部記錄的5%，FTS就比index 快。

5. 點陣圖index，只有類似男，女； 北京，上海，廣州，之類的可以list出來的column 值才可以考慮建bitmap index

6. For example：

B_Tree index

CREATE INDEX hr.employees_last_name_idx
ON hr.employees(last_name)
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K
    PCTINCREASE 0     MAXEXTENTS 50)
TABLESPACE indx;

Bitmap Index

CREATE BITMAP INDEX orders_region_id_idx
ON orders(region_id)
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K
    PCTINCREASE 0     MAXEXTENTS 50)
TABLESPACE indx;

7. Rebuild index

主要還是看index的扁平程度,
Rebuild the index when :
  - deleted entries represent 20% or more of the current entries
  - the index depth is more then 4 levels.
Possible candidate for bitmap index :
  - when distinctiveness is more than 99%
 
條件1
在分析(analyze)指定索引之後，查詢index_stats的height欄位的值，如果這個值>=4,則最好重建（rebuild）這個索引。
雖然這個規則不是總是正確，但如果這個值一直都是不變的，則這個索引也就不需重建。

條件2
在分析(analyze)指定索引之後，查詢index_stats的del_lf_rows和lf_rows的值,如果(del_lf_rows/lf_rows)*100 > = 20，則這個索引也需要重建。
例子：
SQL > analyze index IND_PK validate structure;
 
SQL > select name,height,del_lf_rows,lf_rows,(del_lf_rows/lf_rows) *100 from index_stats;
 
NAME                               HEIGHT DEL_LF_ROWS    LF_ROWS (DEL_LF_ROWS/LF_ROWS)*100
 
------------------------------ ---------- ----------- ---------- -------------------------
 
INDX_PK                                4      277353     990206                28.0096263
SQL> alter index IND_PK rebuild;

ALTER INDEX orders_region_id_idx REBUILD TABLESPACE indx02;
ALTER INDEX orders_id_idx REBUILD ONLINE;

8. 監事index 的使用

SQL> alter index pk_t_2 monitoring usage;
SQL> alter index pk_t_2 nomonitoring usage;
SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE START_MONI
TORING    END_MONITORING
------------------------------ ------------------------------ --- --- ----------
--------- -------------------
PK_T_2                         TES_P                          NO  YES 11/24/2015
 13:40:34 11/24/2015 13:41:24

如果長時間沒有使用的index，可以和應用確認，是否可以刪除。