The following is a neat way to index id1,id2 when id is not null, and index NULL,NULL (which won’t be indexed) when it is:

create unique index myTable_idx on myTable (
case when id is not null then id1 end,
case when id is not null then id2 end );

---

Possibly related:

• What you Ought to Know About CASE in Oracle PL/SQL
• “Between” CASE and DECODE
• Oracle SQL tip
• The Lazy Developer’s Way to Populate a Surrogate Key
• CASE gotcha in Oracle 8i

---

Tagged case, index | Post a Comment