今天看《oracle高效设计》中函数索引的内容，遇到一个简单的问题，如何使用
这个索引：
例子摘自《oracle高效设计》：
create table project
(project_ID number primary key,
teamid number,
job varchar2(100),
status varchar2(20) check (status in ('ACTIVE', 'INACTIVE'))
);
create UNIQUE index
job_unique_in_teamid on project
( case when status = 'ACTIVE' then teamid else null end,
case when status = 'ACTIVE' then job else null end
)
/
insert into project(project_id,teamid,job,status)
values( 1, 10, 'a', 'ACTIVE' );
insert into project(project_id,teamid,job,status)
values( 2, 10, 'a', 'ACTIVE' );
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.SCOTT.JOB_UNIQUE_IN_TEAMID)
我关心的是如何使用这个索引来查询数据，测试如下：
set autotrace traceonly
SELECT * FROM project
WHERE CASE "STATUS" WHEN 'ACTIVE' THEN "TEAMID" ELSE NULL END = 10
AND CASE "STATUS" WHEN 'ACTIVE' THEN "JOB" ELSE NULL END = 'a'
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 90 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PROJECT | 1 | 90 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | JOB_UNIQUE_IN_TEAMID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
