Re: Can a Function-Based Index do this?

Agree,

Perhaps, in addition you could make order type a compressed column on the index.

i.e. leading column and with compress set to 1.

"J.P." <jp_boileau_at_yahoo.com> wrote in message news:7e388bc3.0206030544.508a90de_at_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 - 09:57:12 CDT