Oracle 基于函数的索引
用户在使用Oracle数据库时，最常遇到问题之一就是它对字符大小写敏感。如果在EMP表中，存储的雇员姓名为SMITH，则用户使用小写搜索时，将无法到该行记录。如果用户不能够确定输入数据的格式，那么就会产生一个严重的错误。例如：
SQL> select empno,ename,job,sal from emp
2 where ename='smith';
未选定行
这可以通过使用Oracle字符串函数对其进行转换，然后再使用转换后的数据进行检索。例如：
SQL> select empno,ename,job,sal from emp
2 where upper(ename)=upper('smith');
EMPNO ENAME JOB SAL
--------------- ---------- --------- ----------
7369 SMITH CLERK 800
采用这种方法后，无论用户输入数据时所使用字符的大小写组合如何，都可以使用该语句检索到数据。但是，在使用这样的查询时，由于用户不是基于表中存储的记录进行搜索。即，如果搜索的值不存在表中，那么它就一定也不会在索引中，所以，即使在ENAME列上建立索引，Oracle也会被迫执行全表搜索，为所遇到的各个行计算UPPER函数。
为了解决这个问题，Oracle提供了一种新的索引类型——基于函数的索引。基于函数的索引只是常规的B树索引，但它存放的数据是由表中的数据应用函数后所得，而不是直接存放表中数据本身。
由于在SQL语句中经常使用小写字符串，所以为了加快数据访问速度，应基于LOWER函数建立函数索引。示例如下：
SQL> create index idx_ename
2 on emp(lower(ename));
索引已创建。
在创建这个函数索引之后，如果在查询条件中包含相同的函数，则系统会利用它来提高查询的执行速度。例如，下面的查询在WHERE子句使用函数，并显示其执行计划为例。
SQL> set autotrace on explain
oracle切割字符串函数SQL> select ename,job,sal
2 from emp
3 where lower(ename)='martin';
ENAME JOB SAL
---------- --------- ----------
MARTIN SALESMAN 1250
执行计划
----------------------------------------------------------
Plan hash value: 898688482
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0 )| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ENAME | 1 | | 1 (0) | 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(LOWER("ENAME")='martin')
如果用户在自己的模式中创建基于函数的索引，则必须具有QUERY REWRITE系统权限。如果用户要在其他模式中创建索引，必须具有CREATE ANY INDEX和GLOBAL QUERY REWRITE权限。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论