Oracle優化經典文章------索引原理篇

　　Oracle提供了大量索引選項知道在給定條件下使用哪個選項對於一個應用程序的性能來說非常重要一個錯誤的選擇可能會引發死鎖並導致數據庫性能急劇下降或進程終止而如果做出正確的選擇則可以合理使用資源使那些已經運行了幾個小時甚至幾天的進程在幾分鐘得以完成這樣會使您立刻成為一位英雄這篇文章就將簡單的討論每個索引選項主要有以下內容
　　
　　[] 基本的索引概念
　　
　　查詢DBA_INDEXES視圖可得到表中所有索引的列表注意只能通過USER_INDEXES的方法來檢索模式(schema)的索引訪問USER_IND_COLUMNS視圖可得到一個給定表中被索引的特定列
　　
　　[] 組合索引
　　
　　當某個索引包含有多個已索引的列時稱這個索引為組合（concatented）索引在 Oraclei引入跳躍式掃描的索引訪問方法之前查詢只能在有限條件下使用該索引比如表emp有一個組合索引鍵該索引包含了empnoename和deptno在Oraclei之前除非在where之句中對第一列（empno）指定一個值否則就不能使用這個索引鍵進行一次范圍掃描
　　
　　特別注意在Oraclei之前只有在使用到索引的前導索引時才可以使用組合索引！
　　
　　[] ORACLE ROWID
　　
　　通過每個行的ROWID索引Oracle提供了訪問單行數據的能力ROWID其實就是直接指向單獨行的線路圖如果想檢查重復值或是其他對ROWID本身的引用可以在任何表中使用和指定rowid列
　　
　　[] 限制索引
　　
　　限制索引是一些沒有經驗的開發人員經常犯的錯誤之一在SQL中有很多陷阱會使一些索引無法使用下面討論一些常見的問題
　　
　　 使用不等於操作符（<>!=）
　　
　　下面的查詢即使在cust_rating列有一個索引查詢語句仍然執行一次全表掃描
　　
　　select cust_Idcust_name
　　from　 customers
　　where　cust_rating <> aa;
　　
　　把上面的語句改成如下的查詢語句這樣在采用基於規則的優化器而不是基於代價的優化器（更智能）時將會使用索引
　　
　　select cust_Idcust_name
　　from　 customers
　　where　cust_rating < aa or cust_rating > aa;
　　
　　特別注意通過把不等於操作符改成OR條件就可以使用索引以避免全表掃描
　　
　　 使用IS NULL 或IS NOT NULL
　　
　　使用IS NULL 或IS NOT NULL同樣會限制索引的使用因為NULL值並沒有被定義在SQL語句中使用NULL會有很多的麻煩因此建議開　　 發人員在建表時把需要索引的列設成NOT NULL如果被索引的列在某些行中存在NULL值就不會使用這個索引（除非索引是一個位圖索　　 引關於位圖索引在稍後在詳細討論）
　　
　　 使用函數
　　
　　如果不使用基於函數的索引那麼在SQL語句的WHERE子句中對存在索引的列使用函數時會使優化器忽略掉這些索引
　　
　　下面的查詢不會使用索引（只要它不是基於函數的索引）
　　
　　select empnoenamedeptno
　　from　 emp
　　where　trunc(hiredate)=MAY;
　　
　　把上面的語句改成下面的語句這樣就可以通過索引進行查找
　　
　　select empnoenamedeptno
　　from　 emp
　　where　hiredate<(to_date(MAY)+);
　　
　　 比較不匹配的數據類型
　　
　　比較不匹配的數據類型也是比較難於發現的性能問題之一
　　
　　注意下面查詢的例子account_number是一個VARCHAR類型在account_number字段上有索引下面的語句將執行全表掃描
　　
　　select bank_nameaddresscitystatezip
　　from　 banks
　　where　account_number = ;
　　Oracle可以自動把where子句變成to_number(account_number)=這樣就限制了索引的使用改成下面的查詢就可以使用索引
　　select bank_nameaddresscitystatezip
　　from　 banks
　　where　account_number =;
　　
　　特別注意不匹配的數據類型之間比較會讓Oracle自動限制索引的使用即便對這個查詢執行Explain Plan也不能讓您明白為什麼做了一　　　　　　　 次全表掃描
　　
　　[] 選擇性
　　
　　使用USER_INDEXES視圖該視圖中顯示了一個distinct_keys列比較一下唯一鍵的數量和表中的行數就可以判斷索引的選擇性選擇性越高索引返回的數據就越少
　　
　　[] 群集因子(Clustering Factor)
　　
　　Clustering Factor位於USER_INDEXES視圖中該列反映了數據相對於已索引的列是否顯得有序如果Clustering Factor列的值接近於索引中的樹葉塊(leaf block)的數目表中的數據就越有序如果它的值接近於表中的行數則表中的數據就不是很有序
　　
　　[] 二元高度(Binary height)
　　
　　索引的二元高度對把ROWID返回給用戶進程時所要求的I/O量起到關鍵作用在對一個索引進行分析後可以通過查詢DBA_INDEXES的Blevel列查看它的二元高度二元高度主要隨著表的大小以及被索引的列中值的范圍的狹窄程度而變化索引上如果有大量被刪除的行它的二元高度也會增加更新索引列也類似於刪除操作因為它增加了已刪除鍵的數目重建索引可能會降低二元高度
　　
　　[] 快速全局掃描
　　
　　在Oracle後就可以使用快速全局掃描(Fast Full Scan)這個選項這個選項允許Oracle執行一個全局索引掃描操作快速全局掃描讀取B樹索引上所有樹葉塊初始化文件中的DB_FILE_MULTIBLOCK_READ_COUNT參數可以控制同時被讀取的塊的數目
　　
　　[] 跳躍式掃描
　　
　　從Oraclei開始索引跳躍式掃描特性可以允許優化器使用組合索引即便索引的前導列沒有出現在WHERE子句中索引跳躍式掃描比全索引掃描要快的多下面的程序清單顯示出性能的差別
　　
　　create index skip on emp(jobempno);
　　index created
　　
　　select count(*)
　　from emp
　　where empno=;
　　
　　Elapsed:::
　　
　　Execution Plan
　　　　 SELECT STATEMENT Optimizer=CHOOSE(Cost= Card= Bytes=)
　　　　　SORT(AGGREGATE)
　　　　　　INDEX(FAST FULL SCAN) OF SKIP(NONUNIQUE)
　　
　　Statistics
　　
　　 consistent gets
　　 physical　 reads
　　
　　select /*+ index(emp skip)*/ count(*)
　　from emp
　　where empno=;
　　
　　Elapsed:::
　　
　　Execution Plan
　　　　 SELECT STATEMENT Optimizer=CHOOSE(Cost= Card= Bytes=)
　　　　　SORT(AGGREGATE)
　　　　　　INDEX(SKIP SCAN) OF SKIP(NONUNIQUE)
　　
　　Statistics
　　
　　 consistent gets
　　 physical　 reads
　　
　　[] 索引的類型
　　B樹索引
　　位圖索引
　　HASH索引
　　索引編排表
　　反轉鍵索引
　　基於函數的索引
　　分區索引
　　本地和全局索引
From:http://tw.wingwit.com/Article/program/Oracle/201311/16507.html