oracle索引__oracle

 索引的類型:
   B-樹索引
   位元影像索引
   HASH索引
   索引編排表
   反轉鍵索引
   基於函數的索引
   分區索引
   本地和全域索引

 

 

限制索引(索引失效)

容易引起oracle索引失效的原因很多：(需要具體分析。你可以根據執行計畫來判斷)

1、在索引列上使用函數。如SUBSTR,DECODE,INSTR等，對索引列進行運算.需要建立函數索引就可以解決了。

2、建立的表還沒來得及產生統計資訊，分析一下就好了

3、基於cost的成本分析，訪問的表過小，使用全表掃描的消耗小於使用索引。

4、使用<>、not in 、not exist，對於這三種情況大多數情況下認為結果集很大，一般大於5%-15%就不走索引而走FTS。

5、單獨的>、<。

6、like "%_" 百分比符號在前。

7、單獨引用複合索引裡非第一位置的索引列。

8、字元型欄位為數字時在where條件裡不添加引號。

9、當變數採用的是times變數，而表的欄位採用的是date變數時.或相反情況。

10、索引失效，可以考慮重建索引，rebuild online。

11、B-tree索引 is null不會走,is not null會走,位元影像索引 is null,is not null  都會走、聯合索引 is not null 只要在建立的索引列（不分先後）都會走。

 

12.可能是你的表經常被更新，片段太多，索引佔用空間太大，最佳化器覺得沒有必要用索引了  
  就直接全表掃描了啊，你重建立立索引，就整理了片段了啊，當然就又用索引了

 

限制索引是一些沒有經驗的開發人員經常犯的錯誤之一。在SQL中有很多陷阱會使一些索引無法使用。下面討論一些常見的問題：
4.1 使用不等於操作符（<>、!=）
下面的查詢即使在cust_rating列有一個索引，查詢語句仍然執行一次全表掃描。
       select cust_Id,cust_name
       from   customers
       where  cust_rating <> 'aa';
把上面的語句改成如下的查詢語句，這樣，在採用基於規則的最佳化器而不是基於代價的最佳化器（更智能）時，將會使用索引。
       select cust_Id,cust_name
       from   customers
       where  cust_rating < 'aa' or cust_rating > 'aa';
特別注意：通過把不等於操作符改成OR條件，就可以使用索引，以避免全表掃描。
2 使用IS NULL 或IS NOT NULL
使用IS NULL 或IS NOT NULL同樣會限制索引的使用。因為NULL值並沒有被定義。在SQL語句中使用NULL會有很多的麻煩。因此建議開     發人員在建表時，把需要索引的列設成NOT NULL。如果被索引的列在某些行中存在NULL值，就不會使用這個索引（除非索引是一個位元影像索     引，關於位元影像索引在稍後在詳細討論）。
3 使用函數
如果不使用基於函數的索引，那麼在SQL語句的WHERE子句中對存在索引的列使用函數時，會使最佳化器忽略掉這些索引。
下面的查詢不會使用索引（只要它不是基於函數的索引）
        select empno,ename,deptno
        from   emp
        where  trunc(hiredate)='01-MAY-81';
        把上面的語句改成下面的語句，這樣就可以通過索引進行尋找。
        select empno,ename,deptno
        from   emp
        where  hiredate<(to_date('01-MAY-81')+0.9999);
4 比較不匹配的資料類型
比較不匹配的資料類型也是比較難於發現的效能問題之一。注意下面查詢的例子，account_number是一個VARCHAR2類型，在account_number欄位上有索引。下面的語句將執行全表掃描。
       select bank_name,address,city,state,zip
       from   banks
       where  account_number = 990354;
Oracle可以自動把where子句變成to_number(account_number)=990354，這樣就限制了索引的使用,改成下面的查詢就可以使用索引：
       select bank_name,address,city,state,zip
       from   banks
       where  account_number ='990354';
特別注意：不匹配的資料類型之間比較會讓Oracle自動限制索引的使用，即便對這個查詢執行Explain Plan也不能讓您明白為什麼做了一 次“全表掃描”。