ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 用函数索引构造特殊的约束

用函数索引构造特殊的约束

原创 Linux操作系统 作者:space6212 时间:2019-07-03 20:33:07 0 删除 编辑

最近在看tom的《oracle高效设计》一书,在高效的设计模式一章中,tom介绍了如何利用函数索引来实现一些特殊的约束。


假如有这样一个需求:
表t在status='N'的情况下id必须唯一,否则id可以不唯一。
实现这种需求,可以通过触发器来做,但也可以用函数索引来做到这一点。
请看下例:

SQL> create table t_unique(id number,status char(1));

Table created

SQL> create unique index uni_t_unique on t_unique(decode(status,'N',id,null));

Index created

SQL> insert into t_unique values(1,'Y');

1 row inserted

SQL> insert into t_unique values(1,'Y');

1 row inserted

--当status='Y'时,插入相同ID不会报错

SQL> insert into t_unique values(2,'N');

1 row inserted

SQL> insert into t_unique values(2,'N');

insert into t_unique values(2,'N')

ORA-00001: 违反唯一约束条件 (SUK.UNI_T_UNIQUE)

--当status='N'时,id必须唯一,否则就报错。
SQL> commit;

Commit complete

这样做除了实现了特殊约束外,在某些情况下可以提高性能。
比如:
status='N'表示是活跃数据,用户经常需要查询。
status='Y'表示历史数据,查询基本不会涉及到

用户经常需要通过ID来查询表的活跃数据。为了提高效率,常规方法是在ID列或者(ID,status)建立索引,这样做的不足之处是索引了不必要的数据,
浪费空间,并且只能通过索引范围扫描,效率也比不上唯一索引扫描。

而用函数方法建立的索引不但节省了空间,查询更是可以用到唯一性索引,性能也可以提高。

1* select /*+ index(t_unique) */ * from t_unique where decode(status,'N',id,null)=2

suk@ORACLE9I> /

ID S
---------- -
2 N


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=16)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_UNIQUE' (Cost=1 Card=1
Bytes=16)

2 1 INDEX (RANGE SCAN) OF 'UNI_T_UNIQUE' (UNIQUE) (Cost=1 Ca
rd=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
426 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/231499/viewspace-63781/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    202
  • 访问量
    142980