RE: Function based indexes?

Lex,

Thanks for this. I like it. Never thought of that before.

Henry


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Lex de Haan
Sent: Wednesday, December 07, 2005 3:31 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Function based indexes?



this just reminds me of an example I worked on a while ago with Diana
Lorentz, 
for the SQL Reference, where two columns are involved:
see
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements
_501
0.htm#i2077034
 
and scroll down to this section:
 
Using a Function-based Index to Define Conditional Uniqueness: Example
======================================================================
The following statement creates a unique function-based index on the
oe.orders
table that prevents a customer from taking advantage of promotion ID 2
("blowout
sale") more than once:

CREATE UNIQUE INDEX promo_ix ON orders
   (CASE WHEN promotion_id = 2 THEN customer_id  ELSE NULL END,
    CASE WHEN promotion_id = 2 THEN promotion_id ELSE NULL END);

cheers,

Lex.


--
http://www.freelists.org/webpage/oracle-l

Other related posts: