Re: Can a Function-Based Index do this?

In article <3cfab7aa$1_2_at_nopics.sjc>, "contrapositive" says...
>
>This follows from a previous thread (see: "Testing for Existence of Rows -
>What's Fastest?"). A function based index was recommended but I'm only now
>probing for details.
>
>Basically we want to establish uniqueness on a field value, but the
>uniqueness doesn't apply to the whole table, just to groups of records.
>Consider a table called "orders." For records of order_type "A", we want to
>check that the field po_num is unique for certain records. We have a trigger
>(after insert on "orders") that does this; it looks something like this
>(pseudocode):
>
>IF :new.order_type = "A" THEN
> SELECT count(*)
> INTO num_recs
> FROM orders
> WHERE order_whse = :new.order_whse
> AND cust_id = :new.cust_id
> AND po_num = :new.po_num;
>END IF;
>--I may be using ":new" wrong here; I
>--don't have the code in front of me
>
>IF num_recs > 1 THEN
> RAISE_APPLICATION_ERROR(...)
>END IF;
>

so, you want order_whse, cust_id, po_num to be unique when order_type is A. That would be:

create unique index t_idx on t( decode(order_type,'A',order_whse,null),
                                decode(order_type,'A',cust_id,null),
                                decode(order_type,'A',po_num,null) );

that would create an index that would only have entries for order_type 'A' (so if 100 out of 10,000 rows have A, the cardinality of the index would be 100 -- not 10,000) and order_whse,cust_id,po_num would have to be unique (so po_num is unique within order_whse,cust_id)

Works better then the trigger as the trigger doesn't work in a multi-user environment (you cannot see others uncommitted work hence you have a race condition unless you resort to lots of manual locking)

>So we want to check that no orders with the new po_num already exist, for
>orders for a certain customer from a certain warehouse. Note that it should
>find one record (itself) since this is an after insert trigger. (Doing it
>before the insert causes a mutating table error, so there's actually some
>trickery in place to make this work. We use the old trick of writing values
>to package variables on a before trigger).
>
>I'd like to do away with all this, since two triggers and a package are
>needed--not to mention that the SELECT COUNT(*) takes a long time on this
>table. Can a function-based index on the table handle this? If so, how? All
>I've found on the web talks about case sensitivity and such--nothing like
>this.
>
>Thanks in advance.
>
>-jk
>
>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp