1、ORACLE index索引介紹2008-06-05 22:21概述 索引在各種關系型數據庫系統中都是舉足輕重的組成部分，其對于提高檢索數據的速度起至關重要的作用。在Oracle中，索引基本分為以下幾種：B*Tree索引，反向索引，降序索引，位圖索引，函數索引，interMedia全文索引等。Oracle提供了大量索引選項。知道在給定條件下使用哪個選項對于一個應用程序的性能來說非常重要。一個錯誤的選擇可能會引發死鎖，并導致數據庫性能急劇下降或進程終止。而如果做出正確的選擇，則可以合理使用資源，使那些已經運行了幾個小時甚至幾天的進程在幾分鐘得以完成，這樣會使您立刻成為一位英雄。這篇文章就將簡單的

2、討論每個索引選項。主要有以下內容：1 基本的索引概念查詢DBA_INDEXES視圖可得到表中所有索引的列表，注意只能通過USER_INDEXES的方法來檢索模式(schema)的索引。訪問USER_IND_COLUMNS視圖可得到一個給定表中被索引的特定列。2 組合索引當某個索引包含有多個已索引的列時，稱這個索引為組合（concatented）索引。在 Oracle9i引入跳躍式掃描的索引訪問方法之前，查詢只能在有限條件下使用該索引。比如：表emp有一個組合索引鍵，該索引包含了empno、ename和deptno。在Oracle9i之前除非在where之句中對第一列（empno）指定一個值，否

3、則就不能使用這個索引鍵進行一次范圍掃描。特別注意：在Oracle9i之前，只有在使用到索引的前導索引時才可以使用組合索引！3 ORACLE ROWID通過每個行的ROWID，索引Oracle提供了訪問單行數據的能力。ROWID其實就是直接指向單獨行的線路圖。如果想檢查重復值或是其他對ROWID本身的引用，可以在任何表中使用和指定rowid列。4 限制索引限制索引是一些沒有經驗的開發人員經常犯的錯誤之一。在SQL中有很多陷阱會使一些索引無法使用。下面討論一些常見的問題：4.1 使用不等于操作符（、!=）下面的查詢即使在cust_rating列有一個索引，查詢語句仍然執行一次全表掃描。select

4、 cust_Id,cust_namefrom customerswherecust_rating aa;把上面的語句改成如下的查詢語句，這樣，在采用基于規則的優化器而不是基于代價的優化器（更智能）時，將會使用索引。select cust_Id,cust_namefrom customerswherecust_rating aa;特別注意：通過把不等于操作符改成OR條件，就可以使用索引，以避免全表掃描。4.2 使用IS NULL 或IS NOT NULL使用IS NULL 或IS NOT NULL同樣會限制索引的使用。因為NULL值并沒有被定義。在SQL語句中使用NULL會有很多的麻煩。因此建議

5、開發人員在建表時，把需要索引的列設成NOT NULL。如果被索引的列在某些行中存在NULL值，就不會使用這個索引（除非索引是一個位圖索引，關于位圖索引在稍后在詳細討論）。4.3 使用函數如果不使用基于函數的索引，那么在SQL語句的WHERE子句中對存在索引的列使用函數時，會使優化器忽略掉這些索引。下面的查詢不會使用索引（只要它不是基于函數的索引）select empno,ename,deptnofrom empwheretrunc(hiredate)=01-MAY-81;把上面的語句改成下面的語句，這樣就可以通過索引進行查找。select empno,ename,deptnofrom empw

6、herehiredate20 and column select number,dump(1,16) from dual union all select number,dump(2,16) from dual union all select number,dump(3,16) from dual;NUMBE DUMP(1,16)- -number Typ=2 Len=2: c1,2 （1）number Typ=2 Len=2: c1,3 （2）number Typ=2 Len=2: c1,4 （3）再對比一下反向以后的情況：SQL select number,dump(reverse(1)

7、,16) from dual2 union all select number,dump(reverse(2),16) from dual3 union all select number,dump(reverse(3),16) from dual;NUMBE DUMP(REVERSE(1),1- -number Typ=2 Len=2: 2,c1 （1）number Typ=2 Len=2: 3,c1 （2）number Typ=2 Len=2: 4,c1 （3） 我們發現索引碼的結構整個顛倒過來了，這樣1，2，3個索引碼基本上不會出現在同一個葉塊里，所以減少了爭用。不過反向索引又一個缺點就

8、是不能在所有使用常規索引的地方使用。在范圍搜索中其不能被使用，例如，where columnvalue,因為在索引的葉塊中索引碼沒有分類，所以不能通過搜索相鄰葉塊完成區域掃描。降序索引 降序索引是8i里面新出現的一種索引，是B*Tree的另一個衍生物,它的變化就是列在索引中的儲存方式從升序變成了降序，在某些場合下降序索引將會起作用。舉個例子，我們來查詢一張表并進行排序：SQL select * from test where a between 1 and 100 order by a desc，b asc; 已選擇100行。Execution Plan- 0 SELECT STATEMENT

9、 Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400) 1 0 SORT(ORDER BY)(Cost=2 Card=100 Bytes=400) 2 1 INDEX (RANGE SCAN) OF IND_BT (NON-UNIQUE) (Cost=2 Card=100 Bytes=400) 這里優化器首先選擇了一個索引范圍掃描,然后還有一個排序的步驟。如果使用了降序索引，排序的過程會被取消。SQL create index test.ind_desc on test.testrev(a desc，b asc); 索引已創建。SQL analyze ind

10、ex test.ind_desc compute statistics; 索引已分析 再來看下執行路徑：SQL select * from test where a between 1 and 100 order by a desc，b asc; 已選擇100行。Execution Plan（SQL執行計劃，稍后會講解如何使用）。- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400)1 0 INDEX (RANGE SCAN) OF IND_DESC (NON-UNIQUE) (Cost=2 Card=100 Byte

11、s=400) 我們看到排序過程消失了，這是因為創建降序索引時Oracle已經把數據都按降序排好了。 另外一個需要注意的地方是要設置init.ora里面的compatible參數為8.1.0或以上，否則創建時desc關鍵字將被忽略。位圖索引 位圖索引主要用于決策支持系統或靜態數據，不支持行級鎖定。位圖索引最好用于低cardinality列（即列的唯一值除以行數為一個很小的值，接近零），例如又一個“性別”列，列值有“Male”，“Female”，“Null”等3種，但一共有300萬條記錄，那么3/3000000約等于0，這種情況下最適合用位圖索引。 位圖索引可以是簡單的（單列）也可以是連接的（多列

12、），但在實踐中絕大多數是簡單的。在這些列上多位圖索引可以與AND或OR操作符結合使用。位圖索引使用位圖作為鍵值，對于表中的每一數據行位圖包含了TRUE（1）、FALSE（0）、或NULL值。位圖索引的位圖存放在B-Tree結構的頁節點中。B-Tree結構使查找位圖非常方便和快速。另外，位圖以一種壓縮格式存放，因此占用的磁盤空間比B-Tree索引要小得多。位圖索引的格式如表26-1所示。 表26-1 位圖索引的格式 行值 1 2 3 4 5 6 7 8 9 10Male 1 0 0 0 0 0 0 0 1 1Female 0 1 1 1 0 0 1 1 0 0Null 0 0 0 0 1 1 0

13、 0 0 0如果搜索where gender=Male,要統計性別是”Male”的列行數的話，Oracle很快就能從位圖中找到共3行即第1，9，10行是符合條件的；如果要搜索where gender=Male or gender=Female的列的行數的話，也很容易從位圖中找到共8行即1，2，3，4，7，8，9，10行是符合條件的。如果要搜索表的值的話，那么Oracle會用內部的轉換函數將位圖中的相關信息轉換成rowid來訪問數據塊函數索引基于函數的索引也是8i以來的新產物，它有索引計算列的能力，它易于使用并且提供計算好的值，在不修改應用程序的邏輯上提高了查詢性能。使用基于函數的索引有幾個先決

14、條件：1）必須擁有QUERY REWRITE（本模式下）或GLOBAL QUERY REWRITE（其他模式下）權限。2）必須使用基于成本的優化器，基于規則的優化器將被忽略。3）必須設置以下兩個系統參數：QUERY_REWRITE_ENABLED=TRUEQUERY_REWRITE_INTEGRITY=TRUSTED可以通過alter system set,alter session set在系統級或線程級設置，也可以通過在init.ora添加實現。這里舉一個基于函數的索引的例子：SQL create index test.ind_fun on test.testindex(upper(a);

15、索引已創建。SQL insert into testindex values(a,2);已創建 1 行。SQL commit;提交完成。SQL select /*+ RULE*/* FROM test.testindex where upper(a)=A;A B- -a 2Execution Plan- 0SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF TESTINDEX(優化器選擇了全表掃描)-SQL select * FROM test.testindex where upper(a)=A;A B- -a 2

16、Execution Plan- 0SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5) 1 0 TABLE ACCESS (BY INDEX ROWID) OF TESTINDEX (Cost=2 Card= 1 Bytes=5) 2 1 INDEX (RANGE SCAN) OF IND_FUN (NON-UNIQUE) (Cost=1 Car d=1)(使用了ind_fun索引)各種索引的創建方法 （1）*Tree索引。 Create index indexname on tablename(columnnamecolumnna

17、me.) （2）反向索引。 Create index indexname on tablename(columnnamecolumnname.) reverse （3）降序索引。 Create index indexname on tablename(columnname DESCcolumnname.) （4）位圖索引。 Create BITMAP index indexname on tablename(columnnamecolumnname.) （5）函數索引。 Create index indexname on tablename(functionname(columnname) 注

18、意：創建索引后分析要索引才能起作用。 analyze index indexname compute statistics;各種索引使用場合及建議（1）B*Tree索引。常規索引，多用于oltp系統，快速定位行，應建立于高cardinality列（即列的唯一值除以行數為一個很大的值，存在很少的相同值）。（2）反向索引。B*Tree的衍生產物，應用于特殊場合，在ops環境加序列增加的列上建立，不適合做區域掃描。（3）降序索引。B*Tree的衍生產物，應用于有降序排列的搜索語句中，索引中儲存了降序排列的索引碼，提供了快速的降序搜索。（4）位圖索引。位圖方式管理的索引，適用于OLAP（在線分析）和D

19、SS（決策處理）系統，應建立于低cardinality列，適合集中讀取，不適合插入和修改，提供比B*Tree索引更節省的空間。（5）函數索引。B*Tree的衍生產物，應用于查詢語句條件列上包含函數的情況，索引中儲存了經過函數計算的索引碼值。可以在不修改應用程序的基礎上能提高查詢效率。索引什么時候不工作首先要聲明兩個知識點：（1）RBO&CBO。Oracle有兩種執行優化器，一種是RBO(Rule Based Optimizer)基于規則的優化器，這種優化器是基于sql語句寫法選擇執行路徑的；另一種是CBO（Cost Based Optimizer）基于規則的優化器，這種優化器是Oracle根據

20、統計分析信息來選擇執行路徑，如果表和索引沒有進行分析，Oracle將會使用RBO代替CBO；如果表和索引很久未分析，CBO也有可能選擇錯誤執行路徑，不過CBO是Oracle發展的方向，自8i版本來已經逐漸取代RBO.（2）AUTOTRACE。要看索引是否被使用我們要借助Oracle的一個叫做AUTOTRACE功能,它顯示了sql語句的執行路徑，我們能看到Oracle內部是怎么執行sql的，這是一個非常好的輔助工具，在sql調優里廣泛被運用。我們來看一下怎么運用AUTOTRACE： 由于AUTOTRACE自動為用戶指定了Execution Plan,因此該用戶使用AUTOTRACE前必須已經建立

21、了PLAN_TABLE。如果沒有的話，請運行utlxplan.sql腳本（它在$ORACLE_HOME/rdbms/admin目錄中）。 AUTOTRACE可以通過運行plustrce.sql腳本(它在$ORACLE_HOME/sqlplus/admin目錄中)來設置，用sys用戶登陸然后運行plustrce.sql后會建立一個PLUSTRACE角色，然后給相關用戶授予PLUSTRACE角色，然后這些用戶就可以使用AUTOTRACE功能了。 AUTOTRACE的默認使用方法是set autotrace on，但是這方法不總是適合各種場合，特別當返回行數很多的時候。Set autotrace t

22、raceonly提供了只查看統計信息而不查詢數據的功能。SQL set autotrace onSQL select * from test; A- 1Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF TESTStatistics- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 0 bytes sent via SQL*Net to client 0 bytes recei

23、ved via SQL*Net from client 0 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk)rows processedSQL set autotrace traceonlySQL select * from test.test;Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF TESTStatistics- 0 recursive calls 0 db block gets 0 consi

24、stent gets 0 physical reads 0 redo size 0 bytes sent via SQL*Net to client 0 bytes received via SQL*Net from client 0 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk)rows processedHints是Oracle提供的一個輔助用法，按字面理解就是提示的意思，確實它起得作用也是提示優化器按它所提供的關鍵字來選擇執行路徑，特別適用于sql調整的時候。使用方法如下：DELETE|INSERT|SE

25、LECT|UPDATE /*+ hint text hinttext. */具體可參考Oracle SQL Reference。有了前面這些知識點，接下來讓我們來看一下什么時候索引是不起作用的。以下列出幾種情況。（1）類型不匹配時。SQL create table test.testindex (a varchar(2),b number);表已創建。SQL create index ind_cola on test.testindex(a);索引已創建。SQL insert into test.testindex values(1,1);已創建 1 行。SQL commit;提交完成。SQL

26、 analyze table test.testindex compute statistics for all indexes;表已分析。SQL set autotrace on;SQL select /*+RULE */* FROM test.testindex where a=1;(使用基于rule的優化器，數據類型匹配的情況下)A B- -1 1Execution Plan- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (BY INDEX ROWID) OF TESTINDEX 2 1 INDEX (RANGE SC

27、AN) OF IND_COLA (NON-UNIQUE)（使用了索引ind_cola）SQL select /*+RULE */* FROM test.testindex where a=1;（數據類型不匹配的情況）A B- -1 1Execution Plan- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF TESTINDEX（優化器選擇了全表掃描）（2）條件列包含函數但沒有創建函數索引。SQL select /*+ RULE */* FROM test.testindex where upper(a)=

28、 A;（使用了函數upper()在列a上）;A B- -a 2Execution Plan- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF TESTINDEX（優化器選擇全表掃描）-創建基于函數的索引SQL create index test.ind_fun on test.testindex(upper(a);索引已創建。SQL insert into testindex values(a,2);已創建1行。SQL commit;提交完成。SQL select /*+ RULE*/* FROM test.

29、testindex where upper(a)=A;A B- -a 2Execution Plan- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF TESTINDEX(在RULE優化器下忽略了函數索引選擇了全表掃描)-SQL select * FROM test.testindex where upper(a)=A;A B- -a 2Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5) 1 0 TABLE A

30、CCESS (BY INDEX ROWID) OF TESTINDEX (Cost=2 Card= 1 Bytes=5) 2 1 INDEX (RANGE SCAN) OF IND_FUN (NON-UNIQUE) (Cost=1 Car d=1)(CBO優化器使用了ind_fun索引)（3）復合索引中的前導列沒有被作為查詢條件。創建一個復合索引SQL create index ind_com on test.testindex(a,b);索引已創建。SQL select /*+ RULE*/* from test.testindex where a=1;A B- -1 2Execution

31、Plan- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 INDEX (RANGE SCAN) OF IND_COM (NON-UNIQUE)(條件列表包含前導列時使用索引ind_com)SQL select /*+ RULE*/* from test.testindex where b=1;未選定行Execution Plan- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF TESTINDEX(條件列表不包括前導列是選擇全表掃描)-（4）CBO模式下選擇的行數比例

32、過大，優化器采取了全表掃描。SQL select * from test.testindex where a=1;A B- -1 2Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5) 1 0 TABLE ACCESS (FULL) OF TESTINDEX (Cost=1 Card=1 Bytes=5)（表一共2行，選擇比例為50%,所以優化器選擇了全表掃描）下面增加表行數SQL declare i number;2 begin3 for i in 1 . 100 loop4 insert in

33、to test.testindex values (to_char(i),i);5 end loop;6 end;7 /PL/SQL 過程已成功完成。SQL commit;提交完成。SQL select count(*) from test.testindex;COUNT(*)-102SQL select * from test.testindex where a=1;A B- -1 11 2Execution PlanSELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5)1 0 INDEX (RANGE SCAN) OF IND_C

34、OM (NON-UNIQUE) (Cost=1 Card=1 Bytes=5)（表一共102行，選擇比例為2/102=2%,所以優化器選擇了索引掃描）（5）CBO模式下表很久沒分析，表的增長明顯，優化器采取了全表掃描。SQL select * from test.testindex where a like 1%;A B- -1 21 110 1011 1112 1213 1314 1415 1516 1617 1718 1819 19100 100已選擇13行。Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1

35、3 Bytes=52) 1 0 TABLE ACCESS (FULL) OF TESTINDEX (Cost=1 Card=13 Bytes=52)(表一共102行，選擇比例為13/10210%,優化器選擇了全表掃描)增加表行數SQL declare i number;2 begin3 for i in 200 . 1000 loop4 insert into test.testindex values (to_char(i),i);5 end loop;6 end;7 /PL/SQL 過程已成功完成。SQL commit;提交完成。SQL select count(*) from test.

36、testindex;COUNT(*)-903SQL select * from test.testindex where a like 1%;A B- -1 21 110 1011 1112 1213 1314 1415 1516 1617 1718 1819 19100 1001000 1000已選擇14行。Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=13 Bytes=52) 1 0 TABLE ACCESS (FULL) OF TESTINDEX (Cost=1 Card=13 Bytes=52)(表一共

37、903行，選擇比例為14/903 analyze table test.testindex compute statistics for table for all indexed columns for all indexes;表已分析。SQL select * from test.testindex where a like 1%;A B- -1 21 110 10100 1001000 100011 1112 1213 1314 1415 1516 1617 1718 1819 19已選擇14行。Execution Plan- 0 SELECT STATEMENT Optimizer=C

38、HOOSE (Cost=4 Card=24 Bytes=120) 1 0 TABLE ACCESS (BY INDEX ROWID) OF TESTINDEX (Cost=4 Card= 24 Bytes=120) 2 1 INDEX (RANGE SCAN) OF IND_COLA (NON-UNIQUE) (Cost=2 Ca rd=24)（經過分析后優化器選擇了正確的路徑，使用了ind_cola索引）analyze index 時validate structure和compute statistics的區別？分析命令 2009-09-17 10:09 閱讀34評論0 字號： 大大 中中 小小