再上一篇:11.2 B*树索引
上一篇:11.3位图索引
主页
下一篇:11.5应用域索引
再下一篇:11.6关于索引的常见问题和神话
文章列表

11.4基于函数的索引

Oracle 9i 10g编程艺术:深入数据库体系结构

基于函数的索引(function-based index)是Oracle8.11.5中增加的一种索引。现在这已经是标准 版的一个特性,但在Oracle9i Release 2之前的版本中,这还只是企业版的一个特性。
利用基于函数的索引,我们能够对计算得出的列建立索引,并在查询中使用这些索引。简而言之,利 用这种能力,你可以做很多事情,如执行大小写无关的搜索或排序;根据复杂的公式进行搜索;还可以实 现你自己的函数和运算符,然后在此之上执行搜索从而高效地扩展SQL语言。
使用基于函数的索引可能有很多原因,其中主要的原因如下: 使用索引很容易实现,并能立即提交一个值。 可以加快现有应用的速度,而不用修改任何逻辑或查询。

11.4.1重要的实现细节

在Oracle9i Release 1中,要创建和使用基于函数的索引,需要一些初始设置,这与B*树和位图索 引有所不同。
注意 以下内容只适用于Oracle9i Release 1及以前的版本。在Oracle9i Release2和以后的版本中 , 基于函数的索引无需初始设置就可以使用。Oracle9i Release 2 的Oracle SQL Reference手册 在这方面说得不对,它称你需要这些权限,但实际上并不需要。
你必须使用一些系统参数或会话设置,而且必须能创建这些系统参数或会话设置,这需要有以下权限 : 必须有系统权限QUERY REWRITE,从而在你自己的模式中的表上创建基于函数的索引。 必须有系统权限GLOBAL QUERY REWRITE,从而在其他模式中的表上创建基于函数的索引。 要 让 优 化 器 使 用 基 于 函 数 的 索 引 , 必 须 设 置 以 下 会 话 或 系 统 变 量 :
QUERY_REWRITE_ENABLED=TRUE 和 QUERY_REWRITE_INTEGRITY=RUSTED。可以在会话级用 ALTER
SESSION来完成设置,也可以在系统级通过ALTER SYSTEM来设置,还可以在init.ora参数文件 中设 置 。 QUERY_REWRITE_ENABLED 允许 优 化 器 重 写 查 询 来 使 用 基 于 函 数 的 索 引 。 QUERY_REWRITE_INTEGRITY告 诉优化器要“相信“程序员标记为确定性的代码确实是确定性的
(下一节会给出一些例子来说明什么是确定性代码,并指出确定性代码的含义)。如果代码实际 上不 是确定性的(也就是说,给定相同的输入,它会返回不同的输出),通过索引获取得到的 行可能是不正确的。你必须负责确保定义为确定性的函数确实是确定性的。
在所有版本中,以下结论都适用:
使用基于代价的优化器(cost-based optimizer,CBO)。在基于函数的索引中,虚拟列(应 用了函数的列)只对CBO可见,而基于规则的优化器(rule-based optimizer, RBO)不能使 用这些虚拟列。RBO可以利用基于函数的索引中未应用函数的前几列。
对于返回VARCHAR2或RAW 类型的用户编写的函数,使用SUBSTR来约束其返回值,也可以 把SUBSTR隐藏在一个视图中(这是推荐的做法)。同样,下一节会给出这样一个例子。
一旦满足前面的条件,基于函数的索引就很容易使用。只需使用CREATE INDEX命令来创建索引,优 化器会在运行时发现并使用你的索引。

11.4.2一个简单的基于函数的索引例子

考虑以下例子。我们想在EMP表的ENAME列上执行一个大小写无关的搜索。在基于函数的索引引入之 前,我们可能必须采用另外一种完全不同的方式来做到。可能要为EMP表增加一个额外的列,例如名为 UPPER_ENAME 的列。这个列由 INSERT 和 UPDATE 上的一个数据库触发器维护;这个触发器只是设置 NEW.UPPER_NAME := UPPER(:NEW.ENAME)。另外要在这个额外的列上建立索引。但是现在有了基于函数的索 引,就根本不用再增加额外的列了。

首先在SCOTT模式中创建演示性EMP表的一个副本,并在其中增加一些数据:
ops$tkyte@ORA10G> create table emp
2 as
3 select *
4 from scott.emp
5 where 11.0; Table created.
ops$tkyte@ORA10G> insert into emp
2 (empno,ename,job,mgr,hiredate,sal,comm,deptno)
3 select rownum empno,

4 initcap(substr(object_name,11.11.)) ename,
5 substr(object_type,11.9) JOB,
6 rownum MGR,
7 created hiredate,
8 rownum SAL,
9 rownum COMM,
11. (mod(rownum,4)+1)*11. DEPTNO
11. from all_objects
11. where rownum < 10000;
9999 rows created.

接下来,在ENAME列的UPPER值上创建一个索引,这就创建了一个大小写无关的索引:
ops$tkyte@ORA10G> create index emp_upper_idx on emp(upper(ename));
Index created.
最后,前面已经提到了,我们要分析这个表。这是因为,需要利用CBO来使用基于函数的索引。在

Oracle 10g中,从技术上讲这一步不是必须的,因为就会默认使用CBO,而且动态采样会收集所需的信息, 但是最好还是自行收集统计信息。
ops$tkyte@ORA10G> begin
2 dbms_stats.gather_table_stats
3 (user,'EMP',cascade=>true);
4 end;
5 /
PL/SQL procedure successfully completed.
现在就在一个列的UPPER值上建立了一个索引。执行“大小写无关“查询(如以下查询)的任何应用

都能利用这个索引:
ops$tkyte@ORA10G> set autotrace traceonly explain
ops$tkyte@ORA10G> select *
2 from emp

3 where upper(ename) = 'KING'; Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=2 Bytes=92)
11.0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=2 Bytes=92)
2 1 INDEX (RANGE SCAN) OF 'EMP_UPPER_IDX' (INDEX) (Cost=11.Card=2)
这样就能得到索引所能提供的性能提升。在有这个特性之前,EMP表中的每一行都要扫描、改为大写
并进行比较。与之不同,利用UPPER(ENAME)上的索引,查询为索引提供了常量KING,然后只对少量数据执 行区间扫描,并按rowid访问表来得到数据。这是相当快的。

对列上的用户编写的函数建立索引时,能更清楚地看到这种性能提升。Oracle 7.1开始允许在SQL 中使用用户编写的函数,所以我们可以做下面的工作:
SQL> select my_function(ename)
2 from emp
3 where some_other_function(empno) > 11.
4 /
这很棒,因为现在我们能很好地扩展 SQL语言,可以包括应用特定的函数。不过,遗憾的是,有时前
面这个查询的性能并不让人满意。假设EMP表中有11.000行。查询期间函数SOME_OTHER_FUNCTION就会执 行11.000次,每行执行一次。另外,假设这个函数执行时需要百分之一秒的时间,尽管这个查询相对简单 , 现在也至少需要11.秒的时间才能完成。

下面来看一个实际的例子,我们在PL/SQL中实现了一个例程,它对SOUNDEX例程稍有修改。另外, 我们将使用一个包全局变量作为过程中的一个计数器,这样我们就能执行使用了MY_SOUNDEX函数的查询, 并查看这个函数会被调用多少次:
ops$tkyte@ORA10G> create or replace package stats
2 as
3 cnt number default 0;
4 end;
5 /
Package created.
ops$tkyte@ORA10G> 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
9 type vcArray is table of varchar2(11.) index by binary_integer;
11. l_code_table vcArray;
11.
11. begin
11. stats.cnt := stats.cnt+1;
11.
11. l_code_table(1) := 'BPFV';
11. l_code_table(2) := 'CSKGJQXZ';
11. l_code_table(3) := 'DT';
11. l_code_table(4) := 'L';
11. l_code_table(5) := 'MN';
20 l_code_table(6) := 'R';
21
22
23 for i in 1 .. length(p_string)
24 loop
25 exit when (length(l_return_string) = 6);

26 l_char := upper(substr( p_string, i, 1 ) );
27
28 for j in 1 .. l_code_table.count
29 loop
30 if (instr(l_code_table(j), l_char ) > 0 AND j <> l_last_digit)
31 then
32 l_return_string := l_return_string || to_char(j,'fm9');
33 l_last_digit := j;
34 end if;
35 end loop;
36 end loop;
37
38 return rpad( l_return_string, 6, '0' );
39 end;
40 /
Function created.
注意在这个函数中,我们使用了一个新的关键字DETERMINISTIC。这就声明了:前面这个函数在给定
相同的输入时,总会返回完全相同的输出。要在一个用户编写的函数上创建索引,这个关键字是必要的。 我们必须告诉Oracle这个函数是确定性的(DETERMINISTIC),而且在给定相同输入的情况下总会返回一致 的结果。通过这个关键字,就是在告诉Oracle:可以相信这个函数,给定相同的输入,不论做多少次调用 , 它肯定能返回相同的值。如果不是这样,通过索引访问数据时就会得到与全表扫描不同的答案。这种确定 性设置表明在有些函数上是不能建立索引的,例如,我们无法在函数DBMS_RANDOM.RANDOM上创建索引,因 为这是一个随机数生成器。函数DBMS_RANDOM.RANDOM的结果不是确定性的;给定相同的输入,我们会得到 随机的输出。另一方面,第一个例子中所用的内置SQL函数UPPER则是确定性的,所有可以在列的UPPER 值上创建一个索引。

既然有了函数MY_SOUNDEX,下面来看没有索引时表现如何。在此使用了前面创建的EMP表(其中有 大约11.,000行):
ops$tkyte@ORA10G> set timing on
ops$tkyte@ORA10G> set autotrace on explain

ops$tkyte@ORA10G> select ename, hiredate
2 from emp
3 where my_soundex(ename) = my_soundex('Kings')
4 /
ENAME HIREDATE
---------- --------- Ku$_Chunk_ 11.-AUG-04
Ku$_Chunk_ 11.-AUG-04
Elapsed: 00:00:01.07
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=32 Card=100 Bytes=1900)
11.0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=32 Card=100 Bytes=1900)
ops$tkyte@ORA10G> set autotrace off ops$tkyte@ORA10G> set timing off ops$tkyte@ORA10G> set serveroutput on
ops$tkyte@ORA10G> exec dbms_output.put_line( stats.cnt );
19998
PL/SQL procedure successfully completed.
可以看到这个查询花了一秒多的时间执行,而且必须执行全表扫描。函数MY_SOUNDEX被调用了几乎
20,000次(根据计数器得出),每行要调用两次。 下面来看对这个函数建立索引后,速度会有怎样的提高。首先如下创建索引:


2 emp( substr(my_soundex(ename),11.6) )
3 /
Index created.
在这个CREATE INDEX命令中,有意思的是在此使用了SUBSTR函数。这是因为,我们在对一个返回串

的函数建索引。如果对一个返回数字或日期的函数建索引,就没有必须使用这个SUBSTR。如果用户编写的 函数返回一个串,之所以要对这样一个函数使用SUBSTR,原因是这种函数会返回VARCHAR2(4000)类型。这 就太大了,无法建立索引,索引条目必须能在块大小的3/4中放得下。如果尝试对这么大的返回值建索引, 就会收到以下错误(在一个块大小为4KB的表空间中):
ops$tkyte@ORA10G> create index emp_soundex_idx on
2 emp( my_soundex(ename) ) tablespace ts4k;
emp( my_soundex(ename) ) tablespace ts4k
* ERROR at line 2:
ORA-01450: maximum key length (3118) exceeded
这并不是说索引中确实包含那么大的键,而是说对数据库而言键可以有这么大。但是数据库“看得懂

“SUBSTR。它看到 SUBSTR的输入参数为1 和6,知道最大的返回值是6 个字符;因此,它允许创建索引。 你很可能会遇到这种大小问题,特别是对于串联索引。以下是一个例子,在此表空间的块大小为8KB:
ops$tkyte@ORA10G> create index emp_soundex_idx on
2 emp( my_soundex(ename), my_soundex(job) );
emp( my_soundex(ename), my_soundex(job) )
* ERROR at line 2:
ORA-01450: maximum key length (6398) exceeded
在此,数据库认为最大的键为6,398,所以CREATE再一次失败。因此,如果用户编写的函数要返回
一个串,要对这样一个函数建立索引,应当在CREATE INDEX语句中对返回类型有所限制。在这个例子中, 由于知道MY_SOUNDEX最多返回6个字符,所以取前6个字符作为字串。

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

(empno,ename,job,mgr,hiredate,sal,comm,deptno)
select rownum empno, initcap(substr(object_name,11.11.)) ename, substr(object_type,11.9) JOB,
rownum MGR, created hiredate, rownum SAL,
rownum COMM, (mod(rownum,4)+1)*11. DEPTNO
from all_objects where rownum < 10000
call count cpu elapsed disk query current row s
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.03 0.06 0 0
0 0
Execute 1 0.46 0.43 0 15439 948
9999
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 2 0.49 0.50 0 15439 948
9999
但是如果有索引,则需要大约11.2秒:

call count cpu elapsed disk query current row s
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.03 0.04 0 0
0 0
Execute 1 11.11. 11.11. 2 15650 7432
9999
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 2 11.11. 11.11. 2 15650 7432
9999
原因在于管理MY_SOUNDEX函数上的新索引会带来开销,这一方面是因为只要有索引就存在相应的性
能开销(任何类型的索引都会影响插入的性能);另一方面是因为这个索引必须把一个存储过程调用9,999 次。

下面测试这个查询,只需再次运行查询:
ops$tkyte@ORA10G> REM reset our counter
ops$tkyte@ORA10G> exec stats.cnt := 0
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> set timing on ops$tkyte@ORA10G> set autotrace on explain ops$tkyte@ORA10G> select ename, hiredate
2 from emp
3 where substr(my_soundex(ename),11.6) = my_soundex('Kings')
4 /

ENAME HIREDATE
---------- --------- Ku$_Chunk_ 11.-AUG-04
Ku$_Chunk_ 11.-AUG-04
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=11.Bytes=11.)
11.0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=11.Bytes=11.)
2 1 INDEX (RANGE SCAN) OF 'EMP_SOUNDEX_IDX' (INDEX) (Cost=11.Card=35)
ops$tkyte@ORA10G> set autotrace off ops$tkyte@ORA10G> set timing off ops$tkyte@ORA10G> set serveroutput on
ops$tkyte@ORA10G> exec dbms_output.put_line( stats.cnt );
2
PL/SQL procedure successfully completed.
如果对这两个例子做个比较(无索引和有索引),会发现插入受到的影响是:其运行时间是原来的两
倍还多。不过,选择操作则不同,原来需要1秒多的时间,现在几乎是“立即”完成。这里的要点是: 有索引时,插入9,999条记录需要大约两倍多的时间。对用户编程的函数建立索引绝对会
影响插入(和一些更新)的性能。当然,你应该意识到任何索引都会影响性能。例如,我做了
一个简单的测试(没有MY_SOUNDEX 函数),其中只是对ENAME列本身加索引。这就导致INSERT 花了大约1 秒的时间执行,因此整个开销并不能全部归咎于PL/SQL函数。由于大多数应用都只 是插入和更新单个的条目,而且插入每一行只会花不到11.11.,000秒的时间,所以在一个经典 的应用中,你可能注意不到这种开销。由于我们一次只插入一行,代价就只是在列上执行一次 函数,而不是像查询数据时那样可能执行数千次。
尽管插入的运行速度慢了两倍多,但查询运行的速度却快了几倍。它只是把MY_SOUNDEX函 数计算了几次,而不是几乎20,000次。这里有索引和无索引时查询性能的差异相当显著。另外 , 表越大,全面扫描查询执行的时间就会越来越长。基于索引的查询则不同,随着表的增大,基 于索引的查询总是有几乎相同的执行性能。
在 我 们 的 查 询 中 必 须 使 用 SUBSTR 。 这 好 像 不 太 好 , 不 如 只 是 写 WHERE MY_SOUNDEX(ename)=MY_SOUNDEX(‘King’)那么直接,但是这个问题可以很容易地得到解决, 稍后就会看到。
因此,插入会受到影响,但是查询运行得快得多。尽管插入/更新性能稍有下降,但是回报是丰厚的。 另外,如果从不更新MY_SOUNDEX函数调用中涉及到的列,更新就根本没有开销(仅当修改了ENAME列而且 其值确实有改变时,才会调用MY_SOUNDEX)。

现在来看如何让查询不使用SUBSTR函数调用。使用SUBSTR调用可能很容易出错,最终用户必须知道 要从第1个字符起取6个字符作为子串(SUBSTR)。如果使用的子串大小不同,就不会使用这个索引。另外 , 我们可能希望在服务器中控制要索引的字节数。因此我们可以重新实现MY_SOUNDEX函数,如果愿意还可以 索引7 个字节而不是6个。利用一个视图就能非常简单地隐藏SUBSTR,如下所示:
ops$tkyte@ORA10G> create or replace view emp_v
2 as
3 select ename, substr(my_soundex(ename),11.6) ename_soundex, hiredate
4 from emp
5 /
View created.
ops$tkyte@ORA10G> exec stats.cnt := 0; PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> set timing on ops$tkyte@ORA10G> select ename, hiredate
2 from emp_v
3 where ename_soundex = my_soundex('Kings')
4 /

ENAME HIREDATE
---------- --------- Ku$_Chunk_ 11.-AUG-04
Ku$_Chunk_ 11.-AUG-04
Elapsed: 00:00:00.03 ops$tkyte@ORA10G> set timing off
ops$tkyte@ORA10G> exec dbms_output.put_line( stats.cnt )
2
PL/SQL procedure successfully completed.
可以看到这个查询计划与对基表的查询计划是一样的。这里所做的只是将SUBSTR(F(X)),11.6)隐藏
在视图本身中。优化器会识别出这个虚拟列实际上是加了索引的列,并采取“正确”的行动。我们能看到 同样的性能提升和同样的查询计划。使用这个视图与使用基表是一样的,甚至还更好一些,因为它隐藏了 复杂性,并允许我们以后改变SUBSTR的大小。

11.4.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:

ops$tkyte@ORA10G> update big_table set temporary = decode(temporary,'N','Y','N');
1000000 rows updated.

现在检查Y与N地比例:
ops$tkyte@ORA10G> 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
- ---------- ---------- N 1779 .11.
Y 998221 99.82
可以看到,在表的 11.000,000条记录中,只有0.2%的数据应当加索引。如果在TEMPORARY列上使用
传统索引(相对于这个例子中PROCESSED_FLAG列的角色),会发现这个索引有11.000,000个条目,占用了 超过14MB的空间,其高度为3:
ops$tkyte@ORA10G> create index processed_flag_idx
2 on big_table(temporary); Index created.
ops$tkyte@ORA10G> analyze index processed_flag_idx

2 validate structure; Index analyzed.
ops$tkyte@ORA10G> select name, btree_space, lf_rows, height
2 from index_stats;
NAME BTREE_SPACE LF_ROWS HEIGHT
------------------------------ ----------- ----------
----------
PROCESSED_FLAG_IDX 14528892 1000000 3

通过这个索引获取任何数据都会带来3个I/O才能达到叶子块。这个索引不仅很“宽”,还很“高”。
要得到第一个未处理的记录,必须至少执行4 个I/O(其中3个是对索引的I/O,另外一个是对表的I/O)。 怎么改变这种情况呢?我们要让索引更小一些,而且要更易维护(更新期间的运行时开销更少)。采
用基于函数的索引,我们可以编写一个函数,如果不想对某个给定行加索引,则这个函数就返回NULL;而

对想加索引的行则返回一个非NULL值。例如,由于我们只对列值为N 的记录感兴趣,所以只对这些记录加 索引:
ops$tkyte@ORA10G> drop index processed_flag_idx;
Index dropped.
ops$tkyte@ORA10G> create index processed_flag_idx
2 on big_table( case temporary when 'N' then 'N' end ); Index created.
ops$tkyte@ORA10G> analyze index processed_flag_idx
2 validate structure; Index analyzed.
ops$tkyte@ORA10G> select name, btree_space, lf_rows, height
2 from index_stats;
NAME BTREE_SPACE LF_ROWS HEIGHT

------------------------------ ----------- ----------
----------
PROCESSED_FLAG_IDX 40012 1779 2
这就有很大不同,这个索引只有大约 40KB,而不是11..5MB。高度也有所降低。与前面那个更高的索
引相比,使用这个索引能少执行一个I/O。

11.4.4实现有选择的惟一性

要利用基于函数的索引,还有一个有用的技术,这就是使用这种索引来保证某种复杂的约束。例如, 假设有一个带版本信息的表,如项目表。项目有两种状态:要么为ACTIVE,要么为INACTIVE。需要保证以 下规则:“活动的项目必须有一个惟一名;而不活动的项目无此要求。”也就是说,只有一个活动的“项目X”, 但是如果你愿意,可以有多个名为X的不活动项目。
开发人员了解到这个需求时,第一反应往往是:“我们只需运行一个查询来查看是否有活动项目X, 如果没有,就可以创建一个活动项目X。”如果你读过第7章(介绍并发控制和多版本的内容),就会知道, 这种简单的实现在多用户环境中是不可行的。如果两个人想同时创建一个新的活动项目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列将建立惟一的索引。如果试图创建同名的活动项
目,就会被检测到,而且这根本不会影响对这个表的并发访问。

11.4.5关于 CASE的警告


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

会悄悄地重写为以下更高效的语句:
CASE "TEMPORARY" WHEN 'N' THEN 'N' END
但是这个函数与我们创建的那个函数不再匹配,所以查询无法使用此函数。如果在11..11.0.3中执

行这个简单的测试用例,然后再在11..11.0.4(该版本修正了这个bug)中执行它,结果如下(在 11..11.0.3 中):
ops$tkyte@ORA10GR1> create table t ( x int );
Table created.

ops$tkyte@ORA10GR1> create index t_idx on
2 t( case when x = 42 then 11.end ); Index created.
ops$tkyte@ORA10GR1> set autotrace traceonly explain
ops$tkyte@ORA10GR1> select /*+ index( t t_idx ) */ *
2 from t
3 where (case when x = 42 then 11.end ) = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=11.Bytes=11.)
11.0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=2 Card=11.Bytes=11.)
看上去,基于函数的索引不仅不会工作,而且不可用。但是这个 FBI(基于函数的索引)其实是可用

的,只不过这里底层函数被重写了,我们可以查看视图USER_IND_EXPRESSIONS来看看Oracle是如何重写 它的,从而验证这一点:
ops$tkyte@ORA10GR1> select column_expression
2 from user_ind_expressions
3 where index_name = 'T_IDX'; COLUMN_EXPRESSION
--------------------------------------------------------------------------------
CASE "X" WHEN 42 THEN 11.END

在Oracle11..11.0.4中,基于函数的索引中也会发生重写,但是索引会使用重写后的函数:
ops$tkyte@ORA10G> set autotrace traceonly explain
ops$tkyte@ORA10G> select /*+ index( t t_idx ) */ *

2 from t
3 where (case when x = 42 then 11.end ) = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11.Card=11.Bytes=11.)
11.0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=11.Card=11.Bytes=11.)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=11.Card=1)
这是因为数据库选择不仅重写了CREATE INDEX中的函数,还重写了查询本身使用的函数,因此二者
是匹配的。 在以前的版本中,对此的解决办法有以下几种:
使用DECODE而不是CASE,因为DECODE不会被重写,即所谓的“所见即所得”。 使用最先搜索到的CASE语法(预计到可能会发生的优化)。
但是,倘若优化器没有使用你的基于函数的索引,而且你对此无法做出解释,不知道为什么没有使用 你的函数,在这种情况下,就可以检查USER_IND_EXPRESSIONS视图,验证使用的函数是否正确。

11.4.6关于 ORA-01743的警告


对于基于函数的索引,我注意到这样一个奇怪的现像,如果你要在内置函数 TO_DATE上创建一个索引 , 某些情况下并不能成功创建,例如:
ops$tkyte@ORA10GR1> create table t ( year varchar2(4) );
Table created.
ops$tkyte@ORA10GR1> create index t_idx on t( to_date(year,'YYYY') );
create index t_idx on t( to_date(year,'YYYY') )
* ERROR at line 1:
ORA-01743: only pure functions can be indexed
这看上去很奇怪,因为有时使用TO_DATE创建索引确实是可以的,例如:

ops$tkyte@ORA10GR1> create index t_idx on t( to_date('01'||year,'MMYYYY') );
Index created.

相关的错误信息也很模糊,让人摸不着头脑:
ops$tkyte@ORA10GR1> !oerr ora 1743
01743, 00000, "only pure functions can be indexed"
// *Cause: The indexed function uses SYSDATE or the user environment.
// *Action: PL/SQL functions must be pure (RNDS, RNPS, WNDS, WNPS). SQL
// expressions must not use SYSDATE, USER, USERENV(), or anything
// else dependent on the session state. NLS-dependent functions
// are OK.
我们并没有使用SYSDATE;也没有使用“用户环境”(难道我们用了吗?)。这里并没有使用PL/SQL

函数,而且没有涉及任何与会话状态有关的方面。问题只是在于我们使用的格式:YYYY。给定完全相同的 输入,这种格式可能返回不同的答案,这取决于调用的函数时输入的月份。例如,对于5 月的每个时间:
ops$tkyte@ORA10GR1> select to_char( to_date('2005','YYYY'),
2 'DD-Mon-YYYY HH24:MI:SS' )
3 from dual; TO_CHAR(TO_DATE('200
--------------------
01-May-2005 00:00:00
YYYY格式会返回5月1日,在6月它会返回6月1日,以此类推。这就说明,如果用到 YYYY,TO_DATE
就不是确定性的!这这是无法创建索引的原因:只有在创建一行(或插入/更新一行)的多月它能正确工作 。 所以,这个错误确实归根于用户环境,其中包含当前日期本身。
要在一行基于函数的索引中使用TO_DATE,必须使用一种无歧义的确定性日期格式,而不论当前是哪 一天。

11.4.7基于函数的索引小结

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