Index of Oracle

　　索引( Index )是常見的數據庫對象它的設置好壞使用是否得當極大地影響數據庫應用程序和Database 的性能雖然有許多資料講索引的用法 DBA 和 Developer 們也經常與它打交道但筆者發現還是有不少的人對它存在誤解因此針對使用中的常見問題講三個問題此文所有示例所用的數據庫是 Oracle OPS on HP N series 示例全部是真實數據讀者不需要注意具體的數據大小而應注意在使用不同的方法後數據的比較本文所講基本都是陳詞濫調但是筆者試圖通過實際的例子來真正讓您明白事情的關鍵
　　一講索引並非總是最佳選擇
　　如果發現Oracle 在有索引的情況下沒有使用索引這並不是Oracle 的優化器出錯在有些情況下Oracle 確實會選擇全表掃描（Full Table Scan）而非索引掃描（Index Scan）這些情況通常有
　　 表未做statistics 或者 statistics 陳舊導致 Oracle 判斷失誤
根據該表擁有的記錄數和數據塊數實際上全表掃描要比索引掃描更快
　　對第種情況最常見的例子是以下這句sql 語句
　　select count(*) from mytable;
　　在未作statistics 之前它使用全表掃描需要讀取多個數據塊（一個數據塊是k） 做了statistics 之後使用的是 INDEX (FAST FULL SCAN) 只需要讀取個數據塊但是statistics 做得不好也會導致Oracle 不使用索引
　　第種情況就要復雜得多一般概念上都認為索引比表快比較難以理解什麼情況下全表掃描要比索引掃描快為了講清楚這個問題這裡先介紹一下Oracle 在評估使用索引的代價（cost）時兩個重要的數據CF(Clustering factor) 和 FF(Filtering factor)
　　CF: 所謂 CF 通俗地講就是每讀入一個索引塊要對應讀入多少個數據塊
　　FF: 所謂 FF 就是該sql 語句所選擇的結果集占總的數據量的百分比
　　大約的計算公式是FF * (CF + 索引塊個數) 由此估計出一個查詢 如果使用某個索引會需要讀入的數據塊塊數需要讀入的數據塊越多則 cost 越大Oracle 也就越可能不選擇使用 index （全表掃描需要讀入的數據塊數等於該表的實際數據塊數）
　　其核心就是 CF 可能會比實際的數據塊數量大CF 受到索引中數據的排列方式影響通常在索引剛建立時索引中的記錄與表中的記錄有良好的對應關系CF 都很小在表經過大量的插入修改後這種對應關系越來越亂CF 也越來越大此時需要 DBA 重新建立或者組織該索引
　　如果某個sql 語句以前一直使用某索引較長時間後不再使用一種可能就是 CF 已經變得太大需要重新整理該索引了
　　FF 則是Oracle 根據 statistics 所做的估計比如 mytables 表有萬行其主鍵myid的最小值是最大值是考慮以下sql 語句
　　Select * from mytables where myid>=; 和
　　Select * from mytables where myid>=
　　這兩句看似差不多的 sql 語句對Oracle 而言卻有巨大的差別因為前者的 FF 是% 而後者的 FF 可能只有 %如果它的CF 大於實際的數據塊數則Oracle 可能會選擇完全不同的優化方式而實際上在我們的數據庫上的測試驗證了我們的預測 以下是在HP 上執行時它們的 explain plan:
　　第一句
　　SQL> select * from mytables where myid>=;
　　已選擇行
　　Execution Plan
　　
　　 SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Byt es=)
　　 TABLE ACCESS (FULL) OF MYTABLES (Cost= Card= Byt es=)
　　Statistics
　　
　　 recursive calls
　　 db block gets
　　 consistent gets
　　 physical reads
　　 redo size
　　 bytes sent via SQL*Net to client
　　 bytes received via SQL*Net from client
　　 SQL*Net roundtrips to/from client
　　 sorts (memory)
　　 sorts (disk)
　　 rows processed
　　第二句
　　Execution Plan
　　
　　 SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes= )
　　 TABLE ACCESS (BY INDEX ROWID) OF MYTABLES (Cost= Card=
　　Bytes=)
　　 INDEX (RANGE SCAN) OF PK_MYTABLES (UNIQUE) (Cost= Card=)
　　Statistics
　　
　　 recursive calls
　　 db block gets
　　 consistent gets
　　 physical reads
　　 redo size
　　 bytes sent via SQL*Net to client
　　 bytes received via SQL*Net from client
　　 SQL*Net roundtrips to/from client
　　 sorts (memory)
　　 sorts (disk)
　　 rows processed
　　 顯而易見第句沒有使用索引第句使用了主鍵索引pk_mytables FF的巨大影響由此可見一斑由此想到我們在寫sql 語句時如果預先估計一下 FF 你就幾乎可以預見到 Oracle 會否使用索引
　　二講索引也有好壞
　　索引有 B tree 索引 Bitmap 索引 Reverse b tree 索引 等最常用的是 B tree 索引 B 的全稱是Balanced 其意義是從 tree 的 root 到任何一個leaf 要經過同樣多的 level 索引可以只有一個字段（Single column） 也可以有多個字段（Composite）最多個字段I 還支持 Functionbased index 許多developer 都傾向於使用單列B 樹索引
　　所謂索引的好壞是指
　　索引不是越多越好特別是大量從來或者幾乎不用的索引對系統只有損害OLTP系統每表超過個索引即會降低性能而且在一個sql 中 Oracle 從不能使用超過 個索引
　　很多時候單列索引不如復合索引有效率
　　用於多表連結的字段加上索引會很有作用
　　那麼在什麼情況下單列索引不如復合索引有效率呢？有一種情況是顯而易見的那就是當sql 語句所查詢的列全部都出現在復合索引中時此時由於 Oracle 只需要查詢索引塊即可獲得所有數據當然比使用多個單列索引要快得多（此時這種優化方式被稱為 Index only access path）
　　除此之外呢？我們還是來看一個例子吧
　　在 HP（Oracle ） 上執行以下語句
　　select count() from mytabs where coid>= and issuedate >= to_date ( yyyymmdd)
　　一開始我們有兩個單列索引I_mytabs(coid) I_mytabs(issuedate) 下面是執行情況
　　COUNT()
　　
　　
　　Execution Plan
　　
　　 SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=)
　　 SORT (AGGREGATE)
　　 TABLE ACCESS (BY INDEX ROWID) OF T_MYTABS (Cost= Card
　　= Bytes=)
　　 INDEX (RANGE SCAN) OF I_MYTABS (NONUNIQUE) (Cost=
　　Card=)
　　Statistics
　　
　　 recursive calls
　　 db block gets
　　 consistent gets
　　 physical reads
　　 redo size
　　 bytes sent via SQL*Net to client
　　 bytes received via SQL*Net from client
　　 SQL*Net roundtrips to/from client
　　 sorts (memory)
　　 sorts (disk)
　　 rows processed
　　可以看到它讀取了個數據塊來獲得所查詢的 多行
　　現在去掉這兩個單列索引增加一個復合索引I_mytabs_test ( coid issuedate) 重新執行結果如下
　　COUNT()
　　
　　
　　Execution Plan
　　
　　 SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=)
　　 SORT (AGGREGATE)
　　 INDEX (RANGE SCAN) OF I_MYTABS_TEST (NONUNIQUE) (Cost= Card= Bytes=)
　　Statistics
　　
　　 recursive calls
　　 db block gets
　　 consistent gets
　　 physical reads
　　 redo size
　　 bytes sent via SQL*Net to client
　　 bytes received via SQL*Net from client
　　 SQL*Net roundtrips to/from client
　　 sorts (memory)
　　 sorts (disk)
　　 rows processed
　　可以看到這次只讀取了個數據塊
　　塊對塊這就是在這個例子中單列索引與復合索引的代價之比這個例子提示我們 在許多情況下單列索引不如復合索引有效率
　　可以說在索引的設置問題上其實有許多工作可以做正確地設置索引需要對應用進行總體的分析
　　三講索引再好不用也是白搭
　　拋開前面所說的假設你設置了一個非常好的索引任何傻瓜都知道應該使用它但是Oracle 卻偏偏不用那麼需要做的第一件事情
From:http://tw.wingwit.com/Article/program/Oracle/201311/16841.html