RE: Function based indexes?
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 7 Dec 2005 09:30:42 +0100
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);
INSERT INTO orders (order_id, order_date, customer_id, order_total,
promotion_id)
VALUES (2459, systimestamp, 106, 251, 2);
1 row created.
INSERT INTO orders (order_id, order_date, customer_id, order_total,
promotion_id)
VALUES (2460, systimestamp+1, 106, 110, 2);
insert into orders (order_id, order_date, customer_id, order_total,
promotion_id)
*
ERROR at line 1:
ORA-00001: unique constraint (OE.PROMO_IX) violated
The objective is to remove from the index any rows where the promotion_id is not
equal to 2. Oracle Database does not store in the index any rows where all the
keys are NULL. Therefore, in this example, we map both customer_id and
promotion_id to NULL unless promotion_id is equal to 2. The result is that the
index constraint is violated only if promotion_id is equal to 2 for two rows
with the same customer_id value.
you can do amazing things with these function-based indexes ...
note by the way that I was wrong about the NULLs being stored in the B*-tree;
they are *not*.
I was mixing this up with descending indexes, where NULLs are converted to some
value and thus *are* stored.
(Jonathan was so kind to correct me privately :-)
cheers,
Lex.
---------------------------------------------------------------------
Jonathan Lewis Seminar http://www.naturaljoin.nl/events/seminars.html
---------------------------------------------------------------------
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Bobak, Mark
Not at all. For multiple values, you just need a slightly more complex CASE
statement:
(Borrowing from Jonathan's example)
create index i2 on t2(case n1 when 1 then 1 when 2 then 2 when 3 then 3 end);
Of course, this will work for a small handful of values, but, you don't want to
go overboard....
Hope that helps,
-Mark
BEGIN:VCARD
VERSION:2.1
N:de Haan;Lex
FN:Lex de Haan
ORG:Natural Join B.V.
TEL;WORK;VOICE:+31.30.2515022
TEL;HOME;VOICE:+31.30.2518795
TEL;CELL;VOICE:+31.62.2955714
TEL;WORK;FAX:+31.30.2523366
ADR;WORK:;;Pieter Breughelstraat 10;Utrecht;;3583 SK;Netherlands
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Pieter Breughelstraat 10=0D=0AUtrecht 3583
SK=0D=0ANetherlands
URL;WORK:http://www.naturaljoin.nl
EMAIL;PREF;INTERNET:lex.de.haan@xxxxxxxxxxxxxx
REV:20040224T160439Z
END:VCARD
Other related posts:
