RE: Function based indexes?

From: Henry Poras <henry_at_itasoftware.com>
Date: Thu, 8 Dec 2005 12:09:28 -0500
Message-ID: <00d101c5fc1a$2bb2e220$3800040a@itasoftware.com>

Lex,

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

Henry

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lex de Haan
Sent: Wednesday, December 07, 2005 3:31 AM To: oracle-l_at_freelists.org
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

Received on Thu Dec 08 2005 - 11:09:50 CST