CASE used in CREATE INDEX

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 );

Related articles:

• CASE gotcha in Oracle 8i
• What you Ought to Know About CASE in Oracle PL/SQL
• “Between” CASE and DECODE

Filed in Oracle, Tips on 08 Jul 05 | Tags: case, index

---