当一个查询运行很慢。通过检查where子句，发现其中的一列应用了sql lower函数，lower函数阻止使用该列上现有的索引。你想要创建一
使用场景：
当一个查询运行很慢。通过检查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):
---------------------------------------------------
多奥淘宝客程序免费版拥有淘宝客站点的基本功能，手动更新少，管理简单等优点，适合刚接触网站的淘客们，或者是兼职做淘客们。同样拥有VIP版的模板引擎技 术、强大的文件缓存机制，但没有VIP版的伪原创跟自定义URL等多项创新的搜索引擎优化技术，除此之外也是一款高效的API数据系统实现无人值守全自动 化运行的淘宝客网站程序。4月3日淘宝联盟重新开放淘宝API申请，新用户也可使用了
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;
由Oracle索引来理解ArcSDE索引
Oracle索引技术之如何建立最佳索引
Oracle索引列NULL值引发执行计划该表的测试示例
Oracle索引 主键影响查询速度
Oracle索引扫描
本文永久更新链接地址：
