基于函数的索引

最新推荐文章于 2024-07-13 09:26:28 发布

weixin_33861800

最新推荐文章于 2024-07-13 09:26:28 发布

阅读量527

点赞数

文章标签：数据库 python 测试

原文链接：https://my.oschina.net/u/1862478/blog/1920954

版权

2019独角兽企业重金招聘Python工程师标准>>>

利用基于函数的索引，我们能够对计算得出的列建立索引，并在查询中使用这些索引。
使用基于函数的索引可能有很多原因，其中主要的原因如下：
使用索引很容易实现，并能立即提交一个值。
可以加快现有应用的速度，而不用修改任何逻辑或查询。

使用基于代价的优化器（cost-based optimizer，CBO）。在基于函数的索引中，虚拟列（应用了函数的列）只对CBO可见，而基于规则的优化器（rule-based optimizer， RBO）不能使用这些虚拟列。RBO可以利用基于函数的索引中未应用函数的前几列。
对于返回VARCHAR2或RAW 类型的用户编写的函数，使用SUBSTR来约束其返回值，也可以把SUBSTR隐藏在一个视图中（这是推荐的做法）。

示例：

system@ORCL>create table emp
  2  as
  3  select *
  4  from scott.emp;

表已创建。
system@ORCL>insert into emp
  2  (empno,ename,job,mgr,hiredate,sal,comm,deptno)
  3  select rownum empno,
  4  initcap(substr(object_name,0,5)) ename,
  5  substr(object_type,0,5) JOB,
  6  rownum MGR,
  7  created hiredate,
  8  rownum SAL,
  9  rownum COMM,
 10  (mod(rownum,4)+1)*5  DEPTNO
 11  from all_objects ;

已创建72020行。

system@ORCL>create index emp_upper_idx on emp(upper(ename));

索引已创建。

需要利用CBO来使用基于函数的索引

system@ORCL>begin
  2     dbms_stats.gather_table_stats
  3     (user,'EMP',cascade=>true);
  4  end;
  5  /

PL/SQL 过程已成功完成。

现在就在一个列的UPPER值上建立了一个索引。执行“大小写无关“查询（如以下查询）的任何应用都能利用这个索引：

system@ORCL>set autotrace traceonly explain
system@ORCL>select *
  2  from emp
  3  where upper(ename) = 'KING';

执行计划
----------------------------------------------------------
Plan hash value: 1576737566

--------------------------------------------------------------------------------
-------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU
)| Time     |
--------------------------------------------------------------------------------
-------------
|   0 | SELECT STATEMENT            |               |     4 |   196 |     3   (0
)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP           |     4 |   196 |     3   (0
)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_UPPER_IDX |     4 |       |     1   (0
)| 00:00:01 |
--------------------------------------------------------------------------------
-------------

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

   2 - access(UPPER("ENAME")='KING')

这样就能得到索引所能提供的性能提升。在有这个特性之前，EMP表中的每一行都要扫描、改为大写并进行比较。与之不同，利用UPPER(ENAME)上的索引，查询为索引提供了常量KING，然后只对少量数据执行区间扫描，并按rowid访问表来得到数据。这是相当快的。

对列上的用户编写的函数建立索引时，能更清楚地看到这种性能提升。

select my_function(ename)
from emp
where some_other_function(empno) > 11
/

这很棒，因为现在我们能很好地扩展SQL语言，可以包括应用特定的函数。不过，有时前面这个查询的性能并不让人满意。假设EMP表中有11,000行。查询期间函数SOME_OTHER_FUNCTION就会执行11,000次，每行执行一次。另外，假设这个函数执行时需要百分之一秒的时间，尽管这个查询相对简单，现在也至少需要11秒的时间才能完成。

system@ORCL>create or replace package stats
  2  as
  3     cnt number default 0;
  4  end;
  5  /

程序包已创建。

system@ORCL>create or replace
  2  function my_soundex( p_string in varchar2 ) return varchar2
  3  deterministic
  4  as
  5     l_return_string varchar2(6) default substr( p_string, 1, 1 );
  6     l_char varchar2(1);
  7     l_last_digit number default 0;
  8     type vcArray is table of varchar2(11) index by binary_integer;
  9     l_code_table vcArray;
 10
 11  begin
 12     stats.cnt := stats.cnt+1;
 13
 14     l_code_table(1) := 'BPFV';
 15     l_code_table(2) := 'CSKGJQXZ';
 16     l_code_table(3) := 'DT';
 17     l_code_table(4) := 'L';
 18     l_code_table(5) := 'MN';
 19     l_code_table(6) := 'R';
 20  for i in 1 .. length(p_string)
 21  loop
 22     exit when (length(l_return_string) = 6);
 23     l_char := upper(substr( p_string, i, 1 ) );
 24
 25     for j in 1 .. l_code_table.count
 26             loop
 27                     if (instr(l_code_table(j), l_char ) > 0 AND j <> l_last_
digit)
 28                     then
 29                             l_return_string := l_return_string || to_char(j,
'fm9');
 30                             l_last_digit := j;
 31                     end if;
 32             end loop;
 33  end loop;
 34
 35  return rpad( l_return_string, 6, '0' );
 36  end;
 37  /

函数已创建。

注意在这个函数中，我们使用了一个关键字DETERMINISTIC。这就声明了：前面这个函数在给定相同的输入时，总会返回完全相同的输出。要在一个用户编写的函数上创建索引，这个关键字是必要的。我们必须告诉Oracle这个函数是确定性的（DETERMINISTIC），而且在给定相同输入的情况下总会返回一致的结果。如果不是这样，通过索引访问数据时就会得到与全表扫描不同的答案。这种确定性设置表明在有些函数上是不能建立索引的，例如，我们无法在函数DBMS_RANDOM.RANDOM上创建索引，因为这是一个随机数生成器。函数DBMS_RANDOM.RANDOM的结果不是确定性的；给定相同的输入，我们会得到随机的输出。另一方面，第一个例子中所用的内置SQL函数UPPER则是确定性的，所有可以在列的UPPER值上创建一个索引。

既然有了函数MY_SOUNDEX，下面来看没有索引时表现如何。在此使用了前面创建的EMP表（其中有大约11,000行）：

system@ORCL>set timing on
system@ORCL>set autotrace on explain
system@ORCL>select ename, hiredate
  2  from emp
  3  where my_soundex(ename) = my_soundex('Kings')
  4  /

未选定行

已用时间:  00: 00: 01.21

执行计划
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   720 | 10080 |   162  (20)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| EMP  |   720 | 10080 |   162  (20)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("MY_SOUNDEX"("ENAME")="MY_SOUNDEX"('Kings'))

system@ORCL>set autotrace off
system@ORCL>set timing off
system@ORCL>set serveroutput on
system@ORCL>exec dbms_output.put_line( stats.cnt );
72002

PL/SQL 过程已成功完成。

可以看到这个查询花了一秒多的时间执行，而且必须执行全表扫描。函数MY_SOUNDEX被调用了 72,002 次（根据计数器得出），每行要调用两次。

system@ORCL>create index emp_soundex_idx on
  2  emp( substr(my_soundex(ename),11.6) )
  3  /

索引已创建。

在这个CREATE INDEX命令中，有意思的是在此使用了SUBSTR函数。这是因为，我们在对一个返回串的函数建索引。如果对一个返回数字或日期的函数建索引，就没有必须使用这个SUBSTR。如果用户编写的函数返回一个串，之所以要对这样一个函数使用SUBSTR，原因是这种函数会返回VARCHAR2(4000)类型。这就太大了，无法建立索引，索引条目必须能在块大小的3/4中放得下。

这并不是说索引中确实包含那么大的键，而是说对数据库而言键可以有这么大。但是数据库“看得懂“SUBSTR。它看到SUBSTR的输入参数为1和6，知道最大的返回值是6个字符；因此，它允许创建索引。你很可能会遇到这种大小问题，特别是对于串联索引。以下是一个例子，在此表空间的块大小为8KB：

system@ORCL>create index emp_soundex_idx on
  2  emp( my_soundex(ename), my_soundex(job) );
emp( my_soundex(ename), my_soundex(job) )
                                   *
第 2 行出现错误:
ORA-01450: 超出最大的关键字长度 (6398)

在此，数据库认为最大的键为6,398，所以CREATE失败。因此，如果用户编写的函数要返回一个串，要对这样一个函数建立索引，应当在CREATE INDEX语句中对返回类型有所限制。在这个例子中，由于知道MY_SOUNDEX最多返回6个字符，所以取前6个字符作为字串。

有了这个索引后，现在来测试表的性能。我们想监视索引对INSERT的影响，并观察它能怎样加快SELECT的执行速度。在没有索引的测试用例中，我们的查询用了1秒多的时间，如果在插入期间运行SQL_TRACE和TKPROF，会观察到：在没有索引的情况下，插入9,999条记录耗时约0.5秒。

原因在于管理MY_SOUNDEX函数上的新索引会带来开销，这一方面是因为只要有索引就存在相应的性能开销（任何类型的索引都会影响插入的性能）；另一方面是因为这个索引必须把一个存储过程调用9,999次。

下面测试这个查询，只需再次运行查询：

system@ORCL>REM reset our counter
system@ORCL>exec stats.cnt := 0

PL/SQL 过程已成功完成。

system@ORCL>set timing on
system@ORCL>set autotrace on explain
system@ORCL>select ename, hiredate
  2  from emp
  3  where substr(my_soundex(ename),1,6) = my_soundex('Kings')
  4  /

未选定行

已用时间:  00: 00: 00.03

执行计划
----------------------------------------------------------
Plan hash value: 2259214921

--------------------------------------------------------------------------------
---------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%C
PU)| Time     |
--------------------------------------------------------------------------------
---------------
|   0 | SELECT STATEMENT            |                 |   720 | 15840 |    28
(0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP             |   720 | 15840 |    28
(0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_SOUNDEX_IDX |   288 |       |     1
(0)| 00:00:01 |
--------------------------------------------------------------------------------
---------------

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

   2 - access(SUBSTR("SYSTEM"."MY_SOUNDEX"("ENAME"),1,6)="MY_SOUNDEX"('Kings'))

system@ORCL>set autotrace off
system@ORCL>set timing off
system@ORCL>set serveroutput on
system@ORCL>exec dbms_output.put_line( stats.cnt );
1

PL/SQL 过程已成功完成。

如果对这两个例子做个比较（无索引和有索引），会发现插入受到的影响是：其运行时间是原来的两倍还多。不过，选择操作则不同，原来需要1秒多的时间，现在几乎是“立即”完成。这里的要点是：

有索引时，插入9,999条记录需要大约两倍多的时间。对用户编程的函数建立索引绝对会影响插入（和一些更新）的性能。
尽管插入的运行速度慢了两倍多，但查询运行的速度却快了几倍。它只是把MY_SOUNDEX函数计算了几次，而不是几乎72,000次。这里有索引和无索引时查询性能的差异相当显著。另外，表越大，全面扫描查询执行的时间就会越来越长。基于索引的查询则不同，随着表的增大，基于索引的查询总是有几乎相同的执行性能。
在我们的查询中必须使用SUBSTR。

因此，插入会受到影响，但是查询运行得快得多。尽管插入/更新性能稍有下降，但是回报是丰厚的。另外，如果从不更新MY_SOUNDEX函数调用中涉及到的列，更新就根本没有开销（仅当修改了ENAME列而且其值确实有改变时，才会调用MY_SOUNDEX）。

现在来看如何让查询不使用SUBSTR函数调用。使用SUBSTR调用可能很容易出错，最终用户必须知道要从第1个字符起取6个字符作为子串（SUBSTR）。如果使用的子串大小不同，就不会使用这个索引。另外，我们可能希望在服务器中控制要索引的字节数。因此我们可以重新实现MY_SOUNDEX函数，如果愿意还可以索引7个字节而不是6个。利用一个视图就能非常简单地隐藏SUBSTR，如下所示：

system@ORCL>create or replace view emp_v
  2  as
  3  select ename, substr(my_soundex(ename),1,6) ename_soundex, hiredate
  4  from emp
  5  /

视图已创建。

system@ORCL>exec stats.cnt := 0;

PL/SQL 过程已成功完成。

system@ORCL>set timing on
system@ORCL>select ename, hiredate
  2  from emp_v
  3  where ename_soundex = my_soundex('King')
  4  /

未选定行

system@ORCL>set timing off
system@ORCL>exec dbms_output.put_line( stats.cnt )
1

PL/SQL 过程已成功完成。

可以看到这个查询计划与对基表的查询计划是一样的。这里所做的只是将SUBSTR(F(X)),1,6)隐藏在视图本身中。优化器会识别出这个虚拟列实际上是加了索引的列，并采取“正确”的行动。我们能看到同样的性能提升和同样的查询计划。使用这个视图与使用基表是一样的，甚至还更好一些，因为它隐藏了复杂性，并允许我们以后改变SUBSTR的大小。

3只对部分行建立索引

基于函数的索引除了对使用内置函数（如UPPER、LOWER等）的查询显然有帮助之外，还可以用来有选择地只是对表中的某些行建立索引。

B*树索引对于完全为NULL的键没有相应的条目。也就是说，如果在表T上有一个索引I：

Create index I on t(a,b);

而且行中A和B都为NULL，索引结构中就没有相应的条目。如果只对表中的某些行建立索引，这就能用得上。

考虑有一个很大的表，其中有一个NOT NULL列，名为PROCESSED_FLAG，它有两个可取值：Y或N，默认值为N。增加新行时，这个值为N，指示这一行未得到处理，等到处理了这一行后，则会将其更新为Y来指示已处理。我们可能想对这个列建立索引，从而能快速地获取值为N的记录，但是这里有数百万行，而且几乎所有行的值都为Y。所得到的B*树索引将会很大，如果我们把值从N更新为Y，维护这样一个大索引的开销也相当高。这个表听起来很适合采用位图索引（毕竟基数很低！），但这是一个事务性系统，可能有很多人在同时插入记录（新记录的“是否处理”列设置为N），位图索引不适用于并发修改。如果考虑到这个表中会不断地将N更新为Y，那位图就更不合适了，根本不应考虑，因为这个过程会完全串行化。

所以，我们真正想做的是，只对感兴趣的记录建立索引（即该列值为N的记录）。我们会介绍如何利用基于函数的索引来做到这一点，但是在此之前，先来看如果只是一个常规索引会发生什么。使用本书最前面“环境设置”一节中描述的标准BIG_TABLE脚本，下面更新TEMPORARY列，在此将Y变成N，以及N变成Y：

scott@ORCL>update big_table set temporary = decode(temporary,'N','Y','N');

已更新100000行。

现在检查Y与N地比例：

scott@ORCL>select temporary, cnt,
  2     round( (ratio_to_report(cnt) over ()) * 100, 2 ) rtr
  3  from (
  4             select temporary, count(*) cnt
  5             from big_table
  6             group by temporary
  7     )
  8  /

T        CNT        RTR
- ---------- ----------
Y      99773      99.77
N        227        .23

可以看到，在表中，只有0.2%的数据应当加索引。如果在TEMPORARY列上使用传统索引（相对于这个例子中PROCESSED_FLAG列的角色），会发现这个索引有100,000个条目，占用了超过1.4MB的空间，其高度为2：

scott@ORCL>create index processed_flag_idx
  2  on big_table(temporary);

索引已创建。

scott@ORCL>analyze index processed_flag_idx
  2  validate structure;

索引已分析

scott@ORCL>select name, btree_space, lf_rows, height
  2  from index_stats;

NAME                           BTREE_SPACE    LF_ROWS     HEIGHT
------------------------------ ----------- ---------- ----------
PROCESSED_FLAG_IDX                 1464032     100000          2

通过这个索引获取任何数据都会带来2个I/O才能达到叶子块。这个索引不仅很“宽”，还很“高”。要得到第一个未处理的记录，必须至少执行3个I/O（其中2个是对索引的I/O，另外一个是对表的I/O）。

怎么改变这种情况呢？我们要让索引更小一些，而且要更易维护（更新期间的运行时开销更少）。采用基于函数的索引，我们可以编写一个函数，如果不想对某个给定行加索引，则这个函数就返回NULL；而对想加索引的行则返回一个非NULL值。例如，由于我们只对列值为N的记录感兴趣，所以只对这些记录加索引：

scott@ORCL>drop index processed_flag_idx;

索引已删除。

scott@ORCL>create index processed_flag_idx
  2  on big_table( case temporary when 'N' then 'N' end );

索引已创建。

scott@ORCL>analyze index processed_flag_idx
  2  validate structure;

索引已分析

scott@ORCL>select name, btree_space, lf_rows, height
  2  from index_stats;

NAME                           BTREE_SPACE    LF_ROWS     HEIGHT
------------------------------ ----------- ---------- ----------
PROCESSED_FLAG_IDX                    8000        227          1

这就有很大不同，这个索引只有大约8KB。高度也有所降低。与前面那个更高的索引相比，使用这个索引能少执行一个I/O。

4 实现有选择的惟一性

要利用基于函数的索引，还有一个有用的技术，这就是使用这种索引来保证某种复杂的约束。例如，假设有一个带版本信息的表，如项目表。项目有两种状态：要么为ACTIVE，要么为INACTIVE。需要保证以下规则：“活动的项目必须有一个惟一名；而不活动的项目无此要求。”也就是说，只有一个活动的“项目X”，但是如果你愿意，可以有多个名为X的不活动项目。

开发人员了解到这个需求时，第一反应往往是：“我们只需运行一个查询来查看是否有活动项目X，如果没有，就可以创建一个活动项目X。”这种简单的实现在多用户环境中是不可行的。如果两个人想同时创建一个新的活动项目X，他们都会成功。我们需要将项目X的创建串行化，但是对此惟一的做法是锁住这个项目表（这样做并发性就不太好了），或者使用一个基于函数的索引，让数据库为我们做这个工作。

由于可以在函数上创建索引，而且B*树索引中对于完全为NULL的行没有相应的条目，另外我们可以创建一个UNIQUE索引，基于这几点，可以很容易做到：

Create unique index active_projects_must_be_unique
On projects ( case when status = 'ACTIVE' then name end );

这就行了。状态（status）列是ACTIVE时，NAME列将建立惟一的索引。如果试图创建同名的活动项目，就会被检测到，而且这根本不会影响对这个表的并发访问。

5 关于CASE的警告

某些Oracle版本中有一个bug，其中基于函数的索引中引用的函数会以某种方式被重写，以至于索引无法被透明地使用。例如，前面的CASE语句

Case when temporary = 'N' then 'N' end

会悄悄地重写为以下更高效的语句：

CASE "TEMPORARY" WHEN 'N' THEN 'N' END

scott@ORCL>create table t ( x int );

表已创建。

scott@ORCL>create index t_idx on
  2  t( case when x = 42 then 1 end );

索引已创建。

scott@ORCL>set autotrace traceonly explain
scott@ORCL>select /*+ index( t t_idx ) */ *
  2  from t
  3  where (case when x = 42 then 1 end ) = 1;

执行计划
----------------------------------------------------------
Plan hash value: 470836197

--------------------------------------------------------------------------------
-----
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time
    |
--------------------------------------------------------------------------------
-----
|   0 | SELECT STATEMENT            |       |     1 |    16 |     1   (0)| 00:00
:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    16 |     1   (0)| 00:00
:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00
:01 |
--------------------------------------------------------------------------------
-----

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

   2 - access(CASE "X" WHEN 42 THEN 1 END =1)

Note
-----
   - dynamic sampling used for this statement (level=2)

scott@ORCL>select column_expression
  2  from user_ind_expressions
  3  where index_name = 'T_IDX';

执行计划
----------------------------------------------------------
Plan hash value: 3290151145

--------------------------------------------------------------------------------
---------------------------
| Id  | Operation                          | Name                 | Rows  | Byte
s | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
---------------------------
|   0 | SELECT STATEMENT                   |                      |     2 |   64
8 |   222   (0)| 00:00:03 |
|   1 |  VIEW                              | USER_IND_EXPRESSIONS |     2 |   64
8 |   222   (0)| 00:00:03 |
|   2 |   UNION-ALL                        |                      |       |
  |            |          |
|   3 |    NESTED LOOPS                    |                      |       |
  |            |          |
|   4 |     NESTED LOOPS                   |                      |     1 |    8
2 |   214   (0)| 00:00:03 |
|   5 |      NESTED LOOPS                  |                      |     1 |    7
0 |   213   (0)| 00:00:03 |
|   6 |       NESTED LOOPS                 |                      |     2 |   11
6 |   212   (0)| 00:00:03 |
|   7 |        NESTED LOOPS                |                      |     2 |    9
4 |   208   (0)| 00:00:03 |
|*  8 |         INDEX FAST FULL SCAN       | I_OBJ2               |     2 |    6
0 |   205   (0)| 00:00:03 |
|*  9 |         TABLE ACCESS BY INDEX ROWID| ICOL$                |     1 |    1
7 |     2   (0)| 00:00:01 |
|* 10 |          INDEX RANGE SCAN          | I_ICOL1              |     2 |
  |     1   (0)| 00:00:01 |
|* 11 |        TABLE ACCESS BY INDEX ROWID | OBJ$                 |     1 |    1
1 |     2   (0)| 00:00:01 |
|* 12 |         INDEX RANGE SCAN           | I_OBJ1               |     1 |
  |     1   (0)| 00:00:01 |
|  13 |       TABLE ACCESS BY INDEX ROWID  | COL$                 |     1 |    1
2 |     1   (0)| 00:00:01 |
|* 14 |        INDEX UNIQUE SCAN           | I_COL3               |     1 |
  |     0   (0)| 00:00:01 |
|* 15 |      INDEX UNIQUE SCAN             | I_IND1               |     1 |
  |     0   (0)| 00:00:01 |
|* 16 |     TABLE ACCESS BY INDEX ROWID    | IND$                 |     1 |    1
2 |     1   (0)| 00:00:01 |
|  17 |    NESTED LOOPS                    |                      |       |
  |            |          |
|  18 |     NESTED LOOPS                   |                      |     1 |    8
5 |     8   (0)| 00:00:01 |
|  19 |      NESTED LOOPS                  |                      |     1 |    7
3 |     6   (0)| 00:00:01 |
|  20 |       NESTED LOOPS                 |                      |     1 |    6
1 |     5   (0)| 00:00:01 |
|  21 |        NESTED LOOPS                |                      |     1 |    5
3 |     4   (0)| 00:00:01 |
|* 22 |         INDEX RANGE SCAN           | I_OBJ2               |     1 |    3
6 |     3   (0)| 00:00:01 |
|* 23 |         TABLE ACCESS BY INDEX ROWID| IND$                 |     1 |    1
7 |     1   (0)| 00:00:01 |
|* 24 |          INDEX UNIQUE SCAN         | I_IND1               |     1 |
  |     0   (0)| 00:00:01 |
|* 25 |        INDEX RANGE SCAN            | I_OBJ1               |     1 |
8 |     1   (0)| 00:00:01 |
|  26 |       TABLE ACCESS CLUSTER         | COL$                 |     1 |    1
2 |     1   (0)| 00:00:01 |
|* 27 |        INDEX UNIQUE SCAN           | I_OBJ#               |     1 |
  |     0   (0)| 00:00:01 |
|* 28 |      INDEX RANGE SCAN              | I_ICOL1              |     2 |
  |     1   (0)| 00:00:01 |
|* 29 |     TABLE ACCESS BY INDEX ROWID    | ICOL$                |     1 |    1
2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------
---------------------------

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

   8 - filter("IDX"."NAME"='T_IDX')
   9 - filter(BITAND("IC"."SPARE1",1)=1)
  10 - access("IC"."OBJ#"="IDX"."OBJ#")
  11 - filter("BASE"."NAMESPACE"=1 OR "BASE"."NAMESPACE"=5)
  12 - access("IC"."BO#"="BASE"."OBJ#" AND "BASE"."OWNER#"=USERENV('SCHEMAID'))
  14 - access("C"."OBJ#"="BASE"."OBJ#" AND "IC"."INTCOL#"="C"."INTCOL#")
  15 - access("IDX"."OBJ#"="I"."OBJ#")
  16 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4
OR "I"."TYPE#"=6
              OR "I"."TYPE#"=7 OR "I"."TYPE#"=9) AND BITAND("I"."PROPERTY",1024)
=0)
  22 - access("IDX"."OWNER#"=USERENV('SCHEMAID') AND "IDX"."NAME"='T_IDX' AND "I
DX"."NAMESPACE"=4)
  23 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4
OR "I"."TYPE#"=6
              OR "I"."TYPE#"=7 OR "I"."TYPE#"=9) AND BITAND("I"."PROPERTY",1024)
=0)
  24 - access("IDX"."OBJ#"="I"."OBJ#")
  25 - access("I"."BO#"="BASE"."OBJ#")
       filter("BASE"."OWNER#"<>USERENV('SCHEMAID'))
  27 - access("C"."OBJ#"="BASE"."OBJ#")
  28 - access("IC"."OBJ#"="IDX"."OBJ#")
  29 - filter(BITAND("IC"."SPARE1",1)=1 AND "IC"."INTCOL#"="C"."INTCOL#")

在以前的版本中，对此的解决办法有以下几种：
使用DECODE而不是CASE，因为DECODE不会被重写，即所谓的“所见即所得”。
使用最先搜索到的CASE语法（预计到可能会发生的优化）。
但是，倘若优化器没有使用你的基于函数的索引，可以检查USER_IND_EXPRESSIONS视图，验证使用的函数是否正确。

6 关于ORA-01743的警告

对于基于函数的索引，如果你要在内置函数TO_DATE上创建一个索引，某些情况下并不能成功创建，例如：

scott@ORCL>create table t ( year varchar2(4) );

表已创建。

scott@ORCL>create index t_idx on t( to_date(year,'YYYY') );
create index t_idx on t( to_date(year,'YYYY') )
                                 *
第 1 行出现错误:
ORA-01743: 仅能编制纯函数的索引

这看上去很奇怪，因为有时使用TO_DATE创建索引确实是可以的，例如：

scott@ORCL>create index t_idx on t( to_date('01'||year,'MMYYYY') );

索引已创建。

要在一行基于函数的索引中使用TO_DATE，必须使用一种无歧义的确定性日期格式，而不论当前是哪一天。

7 基于函数的索引小结

基于函数的索引很容易使用和实现，他们能提供立即值。可以用基于函数的索引来加快现有应用的速度，而不用修改应用中的任何逻辑或查询。通过使用基于函数的索引，可以观察到性能会呈数量级地增长。使用这种索引能提前计算出复杂的值，而无需使用触发器。另外，如果在基于函数的索引中物化表达式，优化器就能更准确度估计出选择性。可以使用基于函数的索引有选择地只对感兴趣的几行建立索引（如前面关于PROCESSED_FLAG的例子所示）。实际上，使用这种就是可以对WHERE子句加索引。最后，我们研究了如何使用基于函数的索引来实现某种完整性约束：有选择的惟一性（例如，“每个条件成立时字段X、Y和Z必须惟一”）。
基于函数的索引会影响插入和更新的性能。如果你总是插入数据，而不经常查询，基于函数的索引可能对你并不适用。另一方面，要记住，一般插入时都是一次插入一行，查询却会完成数千次。

转载于:https://my.oschina.net/u/1862478/blog/1920954