Oracle Index 索引介紹 及 最佳化策略__Oracle

Oracle Index 索引介紹

    索引在各種關係型資料庫系統中都是舉足輕重的組成部分，其對於提高檢索資料的速度起至關重要的作用。在Oracle中，索引基本分為以下幾種：B*Tree索引，反向索引，降序索引，位元影像索引，函數索引，interMedia全文索引等。

一個錯誤的選擇可能會引發死結，並導致資料庫效能急劇下降或進程終止。而如果做出正確的選擇，則可以合理使用資源，使那些已經運行了幾個小時甚至幾天的進程在幾分鐘得以完成，這樣會使您立刻成為一位英雄。這篇文章就將簡單的討論每個索引選項。主要有以下內容：

[1] 基本的索引概念

查詢DBA_INDEXES視圖可得到表中所有索引的列表，注意只能通過USER_INDEXES的方法來檢索模式(schema)的索引。訪問USER_IND_COLUMNS視圖可得到一個給定表中被索引的特定列。

[2] 複合式索引

當某個索引包含有多個已索引的列時，稱這個索引為組合（concatented）索引。在 Oracle9i引入跳躍式掃描的索引存取方法之前，查詢只能在有限條件下使用該索引。比如：表emp有一個複合式索引鍵，該索引包含了empno、ename和deptno。在Oracle9i之前除非在where之句中對第一列（empno）指定一個值，否則就不能使用這個索引鍵進行一次範圍掃描。

[3] ORACLE ROWID

通過每個行的ROWID，索引Oracle提供了訪問單行資料的能力。ROWID其實就是直接指向單獨行的線路圖。如果想檢查重複值或是其他對ROWID本身的引用，可以在任何錶中使用和指定rowid列。

[4] 限制索引

限制索引是一些沒有經驗的開發人員經常犯的錯誤之一。在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條件，就可以使用索引，以避免全表掃描。

4.2 使用IS NULL 或IS NOT NULL

使用IS NULL 或IS NOT NULL同樣會限制索引的使用。因為NULL值並沒有被定義。在SQL語句中使用NULL會有很多的麻煩。因此建議開發人員在建表時，把需要索引的列設成NOT NULL。如果被索引的列在某些行中存在NULL值，就不會使用這個索引（除非索引是一個位元影像索引，關於位元影像索引在稍後在詳細討論）。

4.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.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也不能讓您明白為什麼做了一次“全表掃描”。

[5] 選擇性

使用USER_INDEXES視圖，該視圖中顯示了一個distinct_keys列。比較一下唯一鍵的數量和表中的行數，就可以判斷索引的選擇性。選擇性越高，索引返回的資料就越少。

[6] 群集因子(Clustering Factor)

Clustering Factor位於USER_INDEXES視圖中。該列反映了資料相對於已索引的列是否顯得有序。如果Clustering Factor列的值接近於索引中的樹葉塊(leaf block)的數目，表中的資料就越有序。如果它的值接近於表中的行數，則表中的資料就不是很有序。

[7] 二元高度(Binary height)

索引的二元高度對把ROWID返回給使用者進程時所要求的I/O量起到關鍵作用。在對一個索引進行分析後，可以通過查詢DBA_INDEXES的B-level列查看它的二元高度。二元高度主要隨著表的大小以及被索引的列中值的範圍的狹窄程度而變化。索引上如果有大量被刪除的行，它的二元高度也會增加。更新索引列也類似於刪除操作，因為它增加了已刪除鍵的數目。重建索引可能會降低二元高度。

[8] 快速全域掃描

在Oracle7.3後就可以使用快速全域掃描(Fast Full Scan)這個選項。這個選項允許Oracle執行一個全域索引掃描操作。快速全域掃描讀取B-樹索引上所有樹葉塊。初始設定檔案中的DB_FILE_MULTIBLOCK_READ_COUNT參數可以控制同時被讀取的塊的數目。

[9] 跳躍式掃描

從Oracle9i開始，索引跳躍式掃描特性可以允許最佳化器使用複合式索引，即便索引的前置列沒有出現在WHERE子句中。索引跳躍式掃描比全索引掃描要快的多。

[10] 索引的類型

　　B-樹索引

　　位元影像索引

　　HASH索引

　　索引編排表

　　反轉鍵索引

　　基於函數的索引

　　分區索引

　　本地和全域索引
簡要解釋：
    b*tree index：幾乎所有的關係型資料庫中都有b*tree類型索引，也是被最多使用的。其樹結構與二叉樹比較類似，根據rid快速定位所訪問的行。
   反向索引：反轉了b*tree索引碼中的位元組，是索引條目分配更均勻，多用於並行伺服器環境下，用於減少索引葉的競爭。
    降序索引：8i中新出現的索引類型，針對逆向排序的查詢。
    位元影像索引：使用位元影像來管理與資料行的對應關係，多用於OLAP系統。
    函數索引：這種索引中儲存了資料列基於function返回的值，在select * from table where function(column)=value這種類型的語句中起作用。

函數索引

基於函數的索引也是8i以來的新產物，它有索引計算資料行的能力，它便於使用並且提供計算好的值，在不修改應用程式的邏輯上提高了查詢效能。使用基於函數的索引有幾個先決條件：

1）必須擁有QUERY REWRITE（本模式下）或GLOBAL QUERY REWRITE（其他模式下）許可權。

2）必須使用基於成本的最佳化器，基於規則的最佳化器將被忽略。

3）必須設定以下兩個系統參數：

QUERY_REWRITE_ENABLED=TRUE

QUERY_REWRITE_INTEGRITY=TRUSTED

可以通過alter system set,alter session set在系統級或線程級設定，也可以通過在init.ora添加實現。

這裡舉一個基於函數的索引的例子：

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.testindex where upper(a)='A';

A　　     B

-- ----------

a　　     2

Execution Plan

----------------------------------------------------------

   0　　SELECT STATEMENT Optimizer=HINT: RULE

   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX'

　　(最佳化器選擇了全表掃描)

--------------------------------------------------------------------

SQL> select * FROM test.testindex where upper(a)='A';

A　　     B

-- ----------

a　　     2

Execution Plan

----------------------------------------------------------

   0　　SELECT 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(columnname[columnname...])

    （2）反向索引。

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

    （3）降序索引。

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

    （4）位元影像索引。

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

    （5）函數索引。

    Create index indexname on tablename(functionname(columnname))

    注意：建立索引後分析要索引才能起作用。

    analyze index indexname compute statistics;

各種索引使用場合及建議

（1）B*Tree索引。

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

（2）反向索引。

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

（3）降序索引。

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

（4）位元影像索引。

位元影像方式管理的索引，適用於OLAP（線上分析）和DSS（決策處理）系統，應建立於低cardinality列，適合集中讀取，不適合插入和修改，提供比B*Tree索引更節省的空間。

（5）函數索引。

B*Tree的衍生產物，應用於查詢語句條件列上包含函數的情況，索引中儲存了經過Function Compute的索引碼值。可以在不修改應用程式的基礎上能提高查詢效率。

索引什麼時候不工作

首先要聲明兩個知識點：

（1）RBO&CBO。

Oracle有兩種執行最佳化器，一種是RBO(Rule Based Optimizer)基於規則的最佳化器，這種最佳化器是基於sql語句寫法選擇執行路徑的；另一種是CBO（Cost Based Optimizer）基於規則的最佳化器，這種最佳化器是Oracle根據統計分析資訊來選擇執行路徑，如果表和索引沒有進行分析，Oracle將會使用RBO代替CBO；如果表和索引很久未分析，CBO也有可能選擇錯誤執行路徑，不過CBO是Oracle發展的方向，自8i版本來已經逐漸取代RBO.

（2）AUTOTRACE。

要看索引是否被使用我們要藉助Oracle的一個叫做AUTOTRACE功能,它顯示了sql語句的執行路徑，我們能看到Oracle內部是怎麼執行sql的，這是一個非常好的協助工具輔助，在sql調優裡廣泛被運用。我們來看一下怎麼運用AUTOTRACE：

① 由於AUTOTRACE自動為使用者指定了Execution Plan,因此該使用者使用AUTOTRACE前必須已經建立了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 traceonly提供了只查看統計資訊而不查詢資料的功能。

SQL> set autotrace on

SQL> select * from test;

         A

----------

         1

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (FULL) OF 'TEST'

Statistics

----------------------------------------------------------

          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 received via SQL*Net from client

          0 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

rows processed

SQL> set autotrace traceonly

SQL> select * from test.test;

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (FULL) OF 'TEST'

Statistics

----------------------------------------------------------

          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 received via SQL*Net from client

          0 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

rows processed

Hints是Oracle提供的一個輔助用法，按字面理解就是‘提示’的意思，確實它起得作用也是提示最佳化器按它所提供的關鍵字來選擇執行路徑，特別適用於sql調整的時候。使用方法如下：

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

具體可參考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> 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           1

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=HINT: RULE

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX'

   2    1     INDEX (RANGE SCAN) OF 'IND_COLA' (NON-UNIQUE)（使用了索引ind_cola）

――――――――――――――――――――――――――――――――――

SQL> select /*+RULE */* FROM test.testindex where a=1;（資料類型不匹配的情況）

A           B

-- ----------

1           1

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=HINT: RULE

   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX'（最佳化器選擇了全表掃描）

（2）條件列包含函數但沒有建立函數索引。

SQL> select /*+ RULE */* FROM test.testindex where upper(a)= 'A';（使用了函數upper()在列a上）;

A           B

-- ----------

a           2

Execution 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.testindex where upper(a)='A';

A           B

-- ----------

a           2

Execution 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           2

Execution Plan

----------------------------------------------------------

   0      SELECT 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)(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           2

Execution 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）