RE: Function based indexes?
- From: "Henry Poras" <henry@xxxxxxxxxxxxxxx>
- To: <lex.de.haan@xxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 8 Dec 2005 12:09:28 -0500
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
