Re: Can a Function-Based Index do this?

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<tLyK8.46166$0A2.37112_at_rwcrnsc54>...
> How about having a sequence for each order type?
> Then you know it would be unique within an order type and it would scale.
> You wouldn't need a function based index just a regular one.(on order_type
> and po_num) (unique index)
> Jim
>

I cannot see how that would help in this situation, since each new order is of type 'A'. And you don't delve into HOW a unique sequence number would indicate that there is only one order of type 'A' with the given po_num value.  

>
> "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
> >
> >
> >

You could try creating a view of po_num and the associated counts:

create or replace view po_chk as
select order_whse, cust_id, po_num, count(*) ct from orders
where order_type = 'A'
group by order_whse, cust_id, po_num;

Then your query becomes:

    SELECT ct
    INTO num_recs
    FROM po_chk
    WHERE order_whse = :new.order_whse
    AND cust_id = :new.cust_id
    AND po_num = :new.po_num;

Since the count is already computed for you the query should return in far less time.

David Fitzjarrell Received on Mon Jun 03 2002 - 08:29:31 CDT