oracle 实现基于函数的索引

使用场景：
当一个查询运行很慢。通过检查where子句，发现其中的一列应用了sql lower函数，lower函数
阻止使用该列上现有的索引。你想要创建一个基于函数索引来支持这个查询，如下

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';

执行计划
----------------------------------------------------------
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、创建一个基于函数的索引
2、如果使用oracle database 11g或更高版本，创建一个索引虚拟列

下面实现基于函数的索引

创建索引可以通过以下方式来估计索引所使用空间以及所需要分配的空间

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';

执行计划
----------------------------------------------------------
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')

注意：不能直接修改一个创建了基于函数索引的列。需要先删除索引，然后修改列，最后再重建索引。不然会报ora-30556错误(在要修改的列上已定义函数索引或位图联接索引)

查看基于函数的索引定义dba/all/user_ind_expressions

SQL> select index_name,column_expression from user_ind_expressions;

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

接着实现在虚拟列创建一个索引

使用场景

现在正使用一个基于函数的索引，但想要获得更好的性能，想将基于函数的索引替换为一个虚拟列，然后在虚拟列上创建索引（需要11g环境或更高版本）。

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;