Function based indexes? 2005-12-08 - By Henry Poras
Lex,
Thanks for this. I like it. Never thought of that before.
Henry
-- --Original Message-- --
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]
On Behalf Of Lex de Haan
Sent: Wednesday, December 07, 2005 3:31 AM
To: oracle-l@(protected)
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
|
|
