Re: Can a Function-Based Index do this?

Well, my understanding of what he wants to do - and it is a little fuzzy - is that if the order is of type A then he wants a unique sequence for that in the po_num column. I am assuming if it is not an order of type A then he wants a different unique sequence. So in the set of records that are type A orders the po_num column would be unique.

So in the trigger he would assign the po_num column based upon the type of order. For orders of type A he would use one sequence and for all other orders he would use a different sequence.

Jim

"David Fitzjarrell" <oratune_at_msn.com> wrote in message news:32d39fb1.0206030529.5083f322_at_posting.google.com...
> "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 - 14:20:57 CDT