Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Can a Function-Based Index do this?

Re: Can a Function-Based Index do this?

From: J.P. <jp_boileau_at_yahoo.com>
Date: 3 Jun 2002 06:44:52 -0700
Message-ID: <7e388bc3.0206030544.508a90de@posting.google.com>


Why all this mess?

Just create a Unique Index on the two (or more) fields. Voila. (Unless I'm oversimplifying, again).

JP

"contrapositive" <contrapositive_at_hotmail.com> wrote in message news:<3cfab7aa$1_2_at_nopics.sjc>...
> 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 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
Received on Mon Jun 03 2002 - 08:44:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US