作者介紹
丁俊，dbaplus社群聯合發起人，新炬網絡專家團成員，性能優化專家，oracle acea，itpub開發版資深版主。十年電信行業從業經驗，從事過系統開發與維護、業務架構和資料分析、系統優化等工作。電子工業出版社終身榮譽作者，《劍破冰山-oracle開發藝術》副主編。
當我們對列使用了函數運算之後，如果此列沒有函數索引，那麼普通索引是無效的。比如where substr(name,1,3)='abc';如果建立了create index idx_t on t(name);
那麼謂詞是無法使用此索引做範圍掃描的。在oracle中允許定義函數索引（function based index,簡稱fbi)，函數索引可以是基于内置函數的，也可以是自定義函數的，本文主要講述基于自定義函數的索引用法及其注意點。
當需要對列進行複雜的運算，複雜的規則需要自定義函數的時候，如果需要走索引，那麼必須建立自定義函數的索引。建立自定義函數索引有幾點要注意：
1、自定義函數必須加deterministic關鍵字，讓oracle知道此函數對于每個入參的傳回結果都是确定的唯一的。
道理很明顯，如果一樣的入參，結果不同，那麼查詢的結果必然有問題，必須要用這個關鍵字告訴oracle，此函數索引是可以信任的。但是有個問題得注意：因為自定義函數是一系列邏輯規則，就算定義的函數對每個入參傳回的值不唯一（比如用了sysdate，random等運算），但是使用了deterministic關鍵字，讓oracle相信唯一，但是實際情況不唯一，那麼使用函數索引查詢的結果必然也是有問題的。是以使用函數索引要注意：必須從邏輯上确定對于一樣的入參傳回的結果是一樣的，因為oracle不會檢查你的邏輯。
2、一旦改變函數定義，必須rebuild對應的函數索引
很顯然，函數索引中存儲的是表中的列或表達式作為自定義函數的參數的運算結果，如果函數改變，oracle不會自動rebulid函數索引對應的值，這樣如果繼續使用函數索引，必然結果可能出錯。
下面分别對上面的内容舉例說明：
針對第1點的例子：
of course，現在的結果是沒有問題的，但是本身這個自定義函數中的to_date(param,'yyyy')針對不同月份的插入結果傳回的都是當月的第一天，如果我是6月插入:
現在是查詢：
上面的結果是令人迷惑的，因為表裡存儲的有2行2013，但是最終結果卻隻查詢出一行。究其原因，就是自定義函數雖然使用了deterministic關鍵字，但是oracle隻管有沒有這關鍵字，而不會管你的函數邏輯是否真的對每個相同的輸入，有一樣的輸出，這裡我們使用deterministic關鍵字，欺騙了oracle。很顯然，雖然在表裡存儲的2行都是2013，但是一個5月份插入的，一個6月份插入的，通過函數運算，一個索引中存儲的是2013-5-1，一個是2013-6-1，是以使用2013-5-1裡查詢的時候，隻傳回1行。如果自定義中有類似于dbms_random，sys_guid等不确定或随時間變化值不同的，那麼也會産生此混亂結果。
另外很多書上說函數索引必須：
oracle使用函數索引，會進行查詢重寫，要求下面兩個參數開啟：
query_rewrite_enabled=true
query_rewrite_integrity=trusted
經過測試，發現在本環境11g下無影響。
針對第2點的例子：
函數索引的函數定義不能随便改變，改變就必須rebuild函數索引(or删除重建），因為函數索引中會存儲對應函數運算的結果，然後在使用函數索引通路的時候，不用再調用函數，so,函數改變，oracle不會級聯rebuild其函數索引，是以，改變函數邏輯不手動rebuild，必然是危險的。
走全表掃描，函數會對每行都調用1次。
無函數索引，全表掃描，通路對每行都調用函數，一條sql通路函數999次。如果使用函數索引，那麼必然在建立（dml）的時候，會自動調用函數，索引中存儲對應的key與函數運算結果值，是以，再使用到函數索引的時候，不用再調用函數，而且索引通路還提高效率，達到多種提高效率的效果。
使用自定義函數索引是危險的，如果修改函數定義，沒有rebuild或删除重建函數索引，那麼函數索引中存儲的還是舊的函數運算結果，這樣會導緻錯誤：
總結：在不得不使用函數索引來提高效率的時候，别忘記了，随時準備維護函數索引，而且别弄出奇奇怪怪的函數索引，導緻亂七八糟的問題，那樣就不好了！
<b></b>
<b>本文來自雲栖社群合作夥伴"dbaplus"，原文釋出時間：2016-10-09</b>