用函式索引構造特殊的約束

最近在看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

> /

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