oracle 针对部分行创建唯一索引

在数据库的使用中有时会遇到这样的表：用一个字段来标识该列是否有效，标识为有效的数据中，又要求按照某一规则进行唯一性校验。举一个不太恰当的例子的话就是用户表，通过flag字段来标识该行数据是否已被删除，在有效的行中，又要求用户名user_name唯一。

一种常见的解决方法是先建一个唯一约束来检查唯一性，再对user_name字段建一个索引来加快搜索速度。但是有两个缺点：

1. 唯一约束的效率和健壮性完全取决于创建人的经验
2. 索引中存在大量几乎不会被搜索的信息（已经被删除的用户信息不应该再被搜索到）

实际上可以使用函数索引来接受上面两个问题。这主要是利用到了索引不会存储完全为空的行这一特性。

例子

create table test0311(user_name number, flag number);
select * from test0311;
insert into test0311 select level, mod(level, 2) from dual connect by level < 100000;

create unique index idx0311 on test0311(case when flag = 0 then null else user_name end);

select * from test0311 t1 
 where case when flag = 0 then null else user_name end = '637';
;

可以看到查询走的唯一索引

唯一性校验演示

总结：这种写法的优点是，只针对一部分行建立了索引，一个尽可能小的索引对于查询和系统通常是好的。缺点是完全针对一种查询来创建，比如如果想查询user_name = 1 and flag = 0，则这个索引不会起作用，甚至可能带来bug。还有就是函数索引的使用方法本身比较麻烦。