程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle索引整理的詳細描述

Oracle索引整理的詳細描述

編輯:Oracle數據庫基礎

以下的文章主要是對Oracle索引整理的描述,其中包含了Oracle的索引陷阱,以及各種索引使用場合以及建議,還有能用唯一索引,一定用唯一索引有哪些的詳細內容的介紹,以下就好似相關內容的介紹。

一、Oracle的索引陷阱

一個表中有幾百萬條數據,對某個字段加了索引,但是查詢時性能並沒有什麼提高,這主要可能是Oracle的索引限制造成的。

Oracle的索引有一些索引限制,在這些索引限制發生的情況下,即使已經加了索引,Oracle還是會執行一次全表掃描,查詢的性能不會比不加Oracle索引有所提高,反而可能由於數據庫維護索引的系統開銷造成性能更差。

下面是一些常見的索引限制問題。

1、使用不等於操作符(<>, !=)

下面這種情況,即使在列dept_id有一個索引,查詢語句仍然執行一次全表掃描

  1. select * from dept where staff_num <> 1000; 

但是開發中的確需要這樣的查詢,難道沒有解決問題的辦法了嗎?

有!

通過把用 or 語法替代不等號進行查詢,就可以使用Oracle索引,以避免全表掃描:上面的語句改成下面這樣的,就可以使用索引了。

  1. select * from dept shere staff_num < 1000 or dept_id > 1000; 

2、使用 is null 或 is not null

使用 is null 或is nuo null也會限制索引的使用,因為數據庫並沒有定義null值。如果被索引的列中有很多null,就不會使用這個索引(除非索引是一個位圖索引,關於位圖索引,會在以後的blog文章裡做詳細解釋)。在sql語句中使用null會造成很多麻煩。

解決這個問題的辦法就是:建表時把需要索引的列定義為非空(not null)

3、使用函數

如果沒有使用基於函數的索引,那麼where子句中對存在Oracle索引的列使用函數時,會使優化器忽略掉這些索引。下面的查詢就不會使用索引:

  1. select * from staff where trunc(birthdate) 
    = '01-MAY-82'; 

但是把函數應用在條件上,索引是可以生效的,把上面的語句改成下面的語句,就可以通過索引進行查找。

  1. select * from staff where birthdate < 
    (to_date('01-MAY-82') + 0.9999); 

4、比較不匹配的數據類型

比較不匹配的數據類型也是難於發現的性能問題之一。

下面的例子中,dept_id是一個varchar2型的字段,在這個字段上有Oracle索引,但是下面的語句會執行全表掃描。

  1. select * from dept where dept_id = 900198

這是因為Oracle會自動把where子句轉換成to_number(dept_id)=900198,就是3所說的情況,這樣就限制了索引的使用。

把SQL語句改為如下形式就可以使用索引

  1. select * from dept where dept_id = '900198'

二、各種索引使用場合及建議

(1)B*Tree索引。

常規索引,多用於oltp系統,快速定位行,應建立於高cardinality列(即列的唯一值除以行數為一個很大的值,存在很少的相同值)。

  1. Create index indexname on tablename(columnname[columnname...]) 

(2)反向Oracle索引。

B*Tree的衍生產物,應用於特殊場合,在ops環境加序列增加的列上建立,不適合做區域掃描。

  1. Create index indexname on tablename
    (columnname[columnname...]) reverse 

(3)降序索引。

B*Tree的衍生產物,應用於有降序排列的搜索語句中,索引中儲存了降序排列的索引碼,提供了快速的降序搜索。

  1. Create index indexname on tablename(columnname DESC[columnname...]) 

(4)位圖索引。

位圖方式管理的索引,適用於OLAP(在線分析)和DSS(決策處理)系統,應建立於低cardinality列,

適合集中讀取,不適合插入和修改,提供比B*Tree索引更節省的空間。

  1. Create BITMAP index indexname on tablename(columnname[columnname...]) 

在實際應用中,如果某個字段的值需要頻繁更新,那麼就不適合在它上面創建位圖索引。

在位圖Oracle索引中,如果你更新或插入其中一條數值為N的記錄,

那麼相應表中數值為N的記錄(可能成百上千條)全部被Oracle鎖定,

這就意味著其它用戶不能同時更新這些數值為N的記錄,其它用戶必須要等第一個用戶提交後,

才能獲得鎖,更新或插入數據,bitmap index它主要用於決策支持系統或靜態數據。

(5)函數索引。

B*Tree的衍生產物,應用於查詢語句條件列上包含函數的情況,

索引中儲存了經過函數計算的索引碼值。可以在不修改應用程序的基礎上能提高查詢效率。

索引創建策略

1.導入數據後再創建索引

2.不需要為很小的表創建索引

3.對於取值范圍很小的字段(比如性別字段)應當建立位圖索引

4.限制表中的索引的數目

5.為索引設置合適的PCTFREE值

6.存儲索引的表空間最好單獨設定

唯一索引和不唯一索引都只是針對B樹索引而言.

Oracle最多允許包含32個字段的復合Oracle索引

由此估計出一個查詢如果使用某個索引會需要讀入的數據塊塊數。

需要讀入的數據塊越多,則 cost 越大,Oracle 也就越有可能不選擇使用 index

三、能用唯一索引,一定用唯一索引

能加非空,就加非空約束

一定要統計表的信息,索引的信息,柱狀圖的信息。

聯合索引的順序不同,影響Oracle索引的選擇,盡量將值少的放在前面

只有做到以上四點,數據庫才會正確的選擇執行計劃。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved