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

小咪咪 2021-09-25 05:24 389阅读 0赞

在数据库的使用中有时会遇到这样的表：用一个字段来标识该列是否有效，标识为有效的数据中，又要求按照某一规则进行唯一性校验。举一个不太恰当的例子的话就是用户表，通过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';
    ;

可以看到查询走的唯一索引  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25heWlfMjI0_size_16_color_FFFFFF_t_70]  
唯一性校验演示  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25heWlfMjI0_size_16_color_FFFFFF_t_70 1]

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25heWlfMjI0_size_16_color_FFFFFF_t_70]: /images/20210923/25f37d3f8ac84aa585eb66de7e062371.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25heWlfMjI0_size_16_color_FFFFFF_t_70 1]: /images/20210923/59764b5e2cac4c9dba292c30fd3051b9.png