使用場景：
當一個查詢運行很慢。通過檢查where子句，發(fā)現(xiàn)其中的一列應(yīng)用了sql lower函數(shù)，lower函數(shù)
阻止使用該列上現(xiàn)有的索引。你想要創(chuàng)建一個基于函數(shù)索引來支持這個查詢，如下

創(chuàng)新互聯(lián)建站是專業(yè)的任縣網(wǎng)站建設(shè)公司，任縣接單;提供成都做網(wǎng)站、網(wǎng)站建設(shè),網(wǎng)頁設(shè)計,網(wǎng)站設(shè)計,建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進行任縣網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團隊,希望更多企業(yè)前來合作!

SQL> select index_name,column_name from user_ind_columns where table_name='T1';

INDEX_NAME                COLUMN_NAME
------------------------- ------------------------------
T1_PK                     OBJECT_ID

SQL> set autotrace trace explain;
SQL> select * from t1 where lower(object_name)='i_undo1';

執(zhí)行計劃
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   908 |   101K|   436   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   908 |   101K|   436   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(LOWER("OBJECT_NAME")='i_undo1')
從以上可以看出即使該表中有索引也沒有使用。

解決方案
1、創(chuàng)建一個基于函數(shù)的索引
2、如果使用oracle database 11g或更高版本，創(chuàng)建一個索引虛擬列

下面實現(xiàn)基于函數(shù)的索引

創(chuàng)建索引可以通過以下方式來估計索引所使用空間以及所需要分配的空間

SQL> set serveroutput on
SQL> var used_bytes number;
SQL> var allo_bytes number;
SQL> exec dbms_space.create_index_cost('create index t1_object_name on t1(lower(object_name))',:used_bytes,:allo_bytes);
 
PL/SQL procedure successfully completed
used_bytes
---------
2269350
allo_bytes
---------
4194304

SQL>create index idx_lower on t1(lower(object_name)) tablespace index_nocompress;

SQL> select * from t1 where lower(object_name)='i_undo1';

執(zhí)行計劃
----------------------------------------------------------
Plan hash value: 2274688371

-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |   908 |   101K|   193   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1        |   908 |   101K|   193   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_LOWER |   363 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(LOWER("OBJECT_NAME")='i_undo1')

注意：不能直接修改一個創(chuàng)建了基于函數(shù)索引的列。需要先刪除索引，然后修改列，最后再重建索引。不然會報ora-30556錯誤(在要修改的列上已定義函數(shù)索引或位圖聯(lián)接索引)

查看基于函數(shù)的索引定義dba/all/user_ind_expressions

SQL> select index_name,column_expression from user_ind_expressions;

INDEX_NAME                COLUMN_EXPRESSION
------------------------- --------------------------------------------------
IDX_LOWER                 LOWER("OBJECT_NAME")

接著實現(xiàn)在虛擬列創(chuàng)建一個索引

使用場景

現(xiàn)在正使用一個基于函數(shù)的索引，但想要獲得更好的性能，想將基于函數(shù)的索引替換為一個虛擬列，然后在虛擬列上創(chuàng)建索引（需要11g環(huán)境或更高版本）。

SQL>alter table t1 add(lower_object_name generated always as (lower(object_name)) virtual);
SQL>create index idx_lower on t1(lower_object_name) tablespace index_nocompress;