Oracle之函式索引

---

---

在Oracle中，有一類特殊的索引，稱為函式索引（Function-Based Indexes，FBI），它基於對錶中列進行計算後的結果建立索引。函式索引在不修改應用程式的邏輯基礎上提高了查詢效能。如果沒有函式索引，那麼任何在列上執行了函式的查詢都不能使用這個列的索引。當在查詢中包含該函式時，資料庫才會使用該函式索引。函式索引可以是一個B-Tree索引或點陣圖索引。

用於生成索引的函式可以是算術表示式，也可以是一個包含SQL函式、使用者定義PL/SQL函式、包函式，或C呼叫的表示式。當資料庫處理INSERT和UPDATE語句時，它仍然必須計算函式才能完成對語句的處理。

對於函式索引的索引列的函式查詢可以透過檢視DBA_IND_EXPRESSIONS來實現，透過如下的SQL語句可以查詢所有的函式索引：

SELECT * FROM DBA_INDEXES D WHERE D.INDEX_TYPE LIKE 'FUNCTION-BASED%';

函式索引必須遵守下面的規則：

①　必須使用基於成本的最佳化器，而且建立後必須對索引進行分析。

②　如果被函式索引所引用的使用者自定義PL/SQL函式失效了或該函式索引的屬主沒有了在函式索引裡面使用的函式的執行許可權，那麼對這張表上的執行的所有的操作（例如SELECT查詢、DML等）也將失敗（會報錯：ORA-06575: Package or function F_R1_LHR is in an invalid state或ORA-00904: : invalid identifier）。這時，可以重新修改自定義函式並在編譯無報錯透過後，該表上所有的DML和查詢操作將恢復正常。

③　建立函式索引的函式必須是確定性的。即，對於指定的輸入，總是會返回確定的結果。

④　在建立索引的函式里面不能使用SUM、COUNT等聚合函式。

⑤　不能在LOB型別的列、NESTED TABLE列上建立函式索引。

⑥　不能使用SYSDATE、USER等非確定性函式。

⑦　對於任何使用者自定義函式必須顯式的宣告DETERMINISTIC關鍵字，否則會報錯：“ora-30553: the function is not deterministic”。

需要注意的是，使用函式索引有幾個先決條件：

（1）必須擁有CREATE INDEX和QUERY REWRITE（本模式下）或CREATE ANY INDEX和GLOBAL QUERY REWRITE（其它模式下）許可權。其賦權語句分別為“GRANT QUERY REWRITE TO LHR;”和“GRANT GLOBAL QUERY REWRITE TO LHR;”。

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

（3）引數QUERY_REWRITE_INTEGRITY和QUERY_REWRITE_ENABLED可以保持預設值。

QUERY_REWRITE_INTEGRITY = ENFORCED

QUERY_REWRITE_ENABLED = TRUE（從Oracle 10g開始預設為TRUE）

這裡舉一個基於函式的索引的例子。

首先為函式索引的建立及資料做準備：

SYS@lhrdb> CREATE TABLE TESTFINDEX_LHR(ID NUMBER,SCHR VARCHAR2(10));

Table created.

SYS@lhrdb> CREATE INDEX IND_FUN ON TESTFINDEX_LHR(UPPER(SCHR));

Index created.

SYS@lhrdb> INSERT INTO TESTFINDEX_LHR VALUES(1,'a');

1 row created.

SYS@lhrdb> COMMIT;

Commit complete.

因為強制使用基於規則的最佳化器，所以，不會使用函式索引：

SYS@lhrdb> SELECT /*+ RULE*/ * FROM TESTFINDEX_LHR WHERE UPPER(SCHR)='A';

        ID SCHR

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

         1 a

Execution Plan

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

Plan hash value: 940247041

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

| Id  | Operation         | Name           |

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

|   0 | SELECT STATEMENT  |                |

|*  1 |  TABLE ACCESS FULL| TESTFINDEX_LHR |

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

Predicate Information (identified by operation id):

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

   1 - filter(UPPER("SCHR")='A')

Note

-----

   - rule based optimizer used (consider using cbo)

這裡最佳化器選擇了全表掃描，若在不使用基於規則的最佳化器的情況下，則該查詢會選擇函式索引IND_FUN：

SYS@lhrdb> SELECT  * FROM TESTFINDEX_LHR WHERE UPPER(SCHR)='A';

        ID SCHR

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

         1 a

Execution Plan

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

Plan hash value: 967513602

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

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                |     1 |    27 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TESTFINDEX_LHR |     1 |    27 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IND_FUN        |     1 |       |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access(UPPER("SCHR")='A')

Note

-----

   - dynamic sampling used for this statement (level=2)

SYS@lhrdb> SELECT D.TABLE_NAME,D.COLUMN_EXPRESSION FROM DBA_IND_EXPRESSIONS D WHERE D.INDEX_NAME='IND_FUN';

 

TABLE_NAME                     COLUMN_EXPRESSION

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

TESTFINDEX_LHR                 UPPER("SCHR")

可見，例子中使用了IND_FUN函式索引，且函式可以透過檢視DBA_IND_EXPRESSIONS來查詢。

---

---

---

---

Oracle中利用函式索引處理資料傾斜案例 

首先宣告：本方法是受到dbsnake的指導，再次感謝指點。

 

通常來說，索引選取的資料列最好為分散度高、選擇性好。從索引樹結構的角度看，列值都是分佈在葉節點位置。這樣，透過樹結構搜尋得到的葉節點數量效率比較高。

 

實際中，我們常常遇到資料列值傾斜的情況。就是說，整個列資料取值有限。但是大部分資料值都集中在少數一兩個取值裡，其他取值比例極少。比如：一個資料列值有“N”、“B”、“M”、“P”、“Q”幾個取值，其中55%資料行取值為“N”，40%資料行取值為“B”，剩下的取值分佈在5%的資料行中。對於這種結構的資料列加索引，是存在一些問題的。

 

首先，預設資料庫是會為所有的列值（非空）建立索引結構。也就意味著無論是高頻度取值，還是低頻度取值，都會在索引結構的葉節點上出現。當然，這樣的大部分葉節點都是這些重複值。

 

其次，在CBO（基於成本最佳化器）的作用下，對高頻度取值的搜尋一般都不會選擇索引作為搜尋路徑，因為進行全表掃描可能效率更高。我們為資料列建立了索引，但高頻詞的查詢永遠不會走到索引路徑。

 

最後，建立的索引空間和時間消耗比較大。建立的索引涵蓋所有取值，對海量資料表而言，佔有的空間勢必較大。同時，在進行小頻度資料查詢的時候，雖然會去走索引路徑，但是引起的邏輯物理讀也是有一些損耗。

 

 

引入一個解決方法，思路：既然高頻度值在查詢的時候不會走到索引路徑，可以考慮將其剔出構建索引的過程，只為那些低頻度資料值建立索引結構。這樣，建立的索引樹結構相對較小，而且索引查詢的效率也能提升。

 

具體的方法是使用decode函式。decode(a,b,c,d,e…f)含義：如果a=b，則返回c,等於d,返回e,最後沒有匹配的情況下，返回f。針對上面的例子，可以使用decode(列名，‘N’, null, ‘B’, null, 列名)，含義是，如果該列取值為N或者B，直接設定為null，否則才返回列值。並且以此建立函式索引。

 

這樣做藉助了Oracle兩個功能：1、對null值不生成索引；2、函式索引；

 

下面的實驗證明了該方法：

 

1、  構建資料環境

 

//資料準備

SQL> create table t as select * from dba_objects where 1=0;

 

Table created

//構造大資料環境，使用指令碼

declare

  i number;

begin 

  for i in 1..40 loop    

     insert /*+ append */ into t

     select * from dba_objects;

    

     commit; 

  end loop;

end;

/

 

SQL> select count(*) from t;

 

  COUNT(*)

----------

   4759209

 

Executed in 15.522 seconds

 

整理後的資料環境如下：

 

//投入實驗的資料狀態

SQL> select secondary, count(*) from t group by secondary;

 

SECONDARY   COUNT(*)

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

W                273

Q                  9

D                273

T             421230

J            1866592

E                 99

S            2470733

 

7 rows selected

 

Executed in 18.002 seconds

 

可以看到，近五百萬資料兩種，絕大部分資料集中到了S、T、J上，其他資料取值頻數較小。資料傾斜趨勢明顯。

 

2、  建索引

分別對secondary列建立常規、函式索引。

 

SQL>create index IND_SEC_NORMAL on t(secondary);

Index created

SQL> create index ind_t_fun on t(decode (secondary, 'S', null, 'J', null, 'T', null, secondary ));

Index created

Executed in 28.049 seconds

 

索引ind_t_fun將S、T、J值轉化為null，剔出了建立索引的過程。從索引段資訊看，兩個索引所佔的空間差異比較大，也證明了這點。

 

SQL> select * from dba_segments where segment_name='IND_SEC_NORMAL';

 

OWNER    SEGMENT_NAME  SEGMENT_TYPE             BYTES     BLOCKS    EXTENTS

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

SYS      IND_T_FUN     INDEX                 75497472       9216         80  

 

Executed in 0.733 seconds

 

SQL> select * from dba_segments where segment_name=upper('ind_t_fun');

 

OWNER  SEGMENT_NAME  SEGMENT_TYPE          BYTES     BLOCKS    EXTENTS

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

SYS    IND_T_FUN     INDEX                 65536          8          1  

 

Executed in 0.156 seconds

注：本結果經過額外處理，用於方便顯示；

 

可以看出，同樣是對一個資料列加索引。普通索引型別Ind_sec_normal佔據80個區，9216個資料塊，空間約佔75.5M。而函式索引ind_t_fun的空間只用了初始分配的1個區，8個資料塊，空間約佔65K。由此，空間優勢立現！

 

收集統計資料，由於是實驗性質，而且資料量大，採用高取樣率收集統計資訊。

 

SQL> exec dbms_stats.gather_table_stats(user, 'T', cascade => true, estimate_percent => 100,method_opt => 'for all indexed columns');

 

PL/SQL procedure successfully completed

 

Executed in 60.403 seconds

 

 

3、  檢索效率分析

 

針對資料量273的W取值進行分析。

直接索引搜尋：

 

SQL> select * from t where secondary='W';

 

已選擇273行。

 

已用時間:  00: 00: 00.37

 

執行計劃

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

Plan hash value: 1573525374

 

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

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                |   273 | 25935 |    11   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T              |   273 | 25935 |    11   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IND_SEC_NORMAL |   273 |       |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("SECONDARY"='W')

 

統計資訊

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

        775  recursive calls

          0  db block gets

        272  consistent gets

         21  physical reads

          0  redo size

      28339  bytes sent via SQL*Net to client

        583  bytes received via SQL*Net from client

         20  SQL*Net roundtrips to/from client

         16  sorts (memory)

          0  sorts (disk)

        273  rows processed

 

發現採用W作為搜尋值時，是進行了索引搜尋。下面是用函式索引搜尋進行對比。

 

SQL> select * from t where decode(secondary,'S',null,'J',null,'T',null,secondary)='W';

 

已選擇273行。

 

已用時間:  00: 00: 00.04

 

執行計劃

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

Plan hash value: 3192598969

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

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |           |   273 | 25935 |   116   (0)| 00:00:02 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T         |   273 | 25935 |   116   (0)| 00:00:02 |

|*  2 |   INDEX RANGE SCAN          | IND_T_FUN |   273 |       |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access(DECODE("SECONDARY",'S',NULL,'J',NULL,'T',NULL,"SECONDARY")='W')

 

統計資訊

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

         45  recursive calls

          0  db block gets

        140  consistent gets

          0  physical reads

          0  redo size

      13225  bytes sent via SQL*Net to client

        583  bytes received via SQL*Net from client

         20  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

        273  rows processed

 

對比後，我們可以發現，使用函式索引的方法，在執行時間、物理邏輯讀、CPU使用上有一定差異。

 

 

結論：使用函式索引處理偏值方法，在一定長度上最佳化查詢效率和索引結構。上表的資料表明，會使邏輯物理讀的消耗很大程度的減少（索引結構簡化），同時連帶影響執行時間的縮小。因為使用函式要進行計算，CPU使用率相對較高，在可以接受的範圍內。

 

但是，這種方法是存在一些限制的，應用前一定要仔細規劃。

首先，資料表資料要保證較大。因為畢竟函式索引的建立和搜尋較普通索引消耗大，如果資料表小，帶來的最佳化程度不能彌補消耗的成本，結果可能得不償失。筆者進行的一系列實驗中，也發現在資料量中等偏小時，這種效能優勢不能凸顯。

 

其次，列值傾斜趨勢明顯。透過開篇的討論我們不難發現，列值傾斜的程度越高，使用函式索引剔出的資料量也就越大，生成的索引樹結構也就越小越最佳化。這一點是本方法的核心！

 

最後，使用函式索引搜尋時，搜尋的取值頻數越高，最佳化效果越好。在本例中，取值W的列有273行，可以看出明顯的效能最佳化。當我們選擇值有9條資料的Q值時，這種最佳化趨勢可以看到，但是明顯程度降低（實驗結果略）。這裡的原因可能是資料量小時，兩種方法邏輯物理讀的差異度縮小。

---

---

About Me

............................................................................................................................................. ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest ● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： ............................................................................................................................................. ● QQ群號：230161599（滿）、618766405 ● 微信群：可加我微信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友（646634621），註明新增緣由 ● 於 2017-11-01 09:00 ~ 2017-11-30 22:00 在魔都完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................................. ● 小麥苗的微店： ● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/ ............................................................................................................................................. 使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。    小麥苗的微信公眾號      小麥苗的DBA寶典QQ群2     《DBA筆試面寶典》讀者群       小麥苗的微店 .............................................................................................................................................