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 (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 (See ORA-00001.ora-code.com): 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 :-)
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] 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....
