Re: Help needed with slow update

i don't think "DISTINCT" will speed anything up... since this (usually) requires a sort to eliminate duplicates.

you might try replacing

AND T1.PRODUCT_ID = T2.PARENT_ID with

AND P1.PRODUCT_ID = T2.PARENT_ID and check the explain plan.

if PRODUCT_ID is the primary key on the PRODUCT table, is the PRODUCT T1 table even required in the query? i don't see that removing it from the query would have a different result, since you already "know" that the PRODUCT P1 row exists.

UPDATE PRODUCT p1
SET p1.STATUS_CODE = 'HOLD'
WHERE p1.STATUS_CODE <> 'HOLD'
AND p1.STRING1 = 'GROCERY'
AND NOT EXISTS
  (SELECT 1
  FROM PRODUCT t2, PRICING t3
  WHERE p1.PRODUCT_ID = t2.PARENT_ID
  AND t2.PRODUCT_ID = t3.PRODUCT_ID)

from the looks of it, if you want the query to make use of indexes, you'll need a usable index on PARENT_ID of the PRODUCT table, and an index on PRODUCT_ID of the PRICING table.

btw... you probably already know this, but the statement will *not* update STATUS_CODE if it is NULL, because of the inequality comparison operator. to handle NULL values of STATUS_CODE, you can make use of the NVL function.

"joebob" <joebob_at_zipcon.n0t> wrote in message news:983306195.525458_at_news.zipcon.net...
> Thank you Greg,
>
> Would a distinct on the inner select improve performance as well? -joebob
>
> "Greg Vitetzakis" <greg_at_ostnet.com> wrote in message
 news:3a9c05e2$1_at_news1...
> > Often when using the comand "IN" or "Not IN", it prevents oracle from
 using
> > and index effectivly. When the list of items in the IN clause is larger
> > then 50-100
> > replace the IN with an EXISTS.
> >
> > Try this:
> >
> > UPDATE PRODUCT P1
> > SET STATUS_CODE = 'HOLD'
> > WHERE STATUS_CODE <> 'HOLD'
> > AND STRING1 = 'GROCERY'
> > AND NOT EXISTS
> > (SELECT T1.PRODUCT_ID
> > FROM PRODUCT T1, PRODUCT T2, PRICING T3
> > WHERE P1.PRODUCT_ID = T1.PRODUCT_ID
> > AND T1.PRODUCT_ID = T2.PARENT_ID
> > AND T2.PRODUCT_ID = T3.PRODUCT_ID)
> >
> > Greg Vitetzakis V Greg Vitetzakis V.P. Professional Services OSTnet
 (Open
> > Source Technologies Inc.) (514) 278-7678 x246 www.ostnet.com
> > "joebob" <joebob_at_zipcon.n0t> wrote in message
> > news:983301661.746439_at_news.zipcon.net...
> > > This takes about an hour to run even though PRODUCT only holds 32000
 records, PRICING holds 17000 records, and only 2400 records got
> > > updated last time I ran it. Since this needs to get run on a regular
 basis, I need to find some faster ways if there are any. Both
> > > PRODUCT and PRICING are indexed so I need to gain speed somewhere
 else:
> > >
> > > UPDATE PRODUCT SET STATUS_CODE = 'HOLD'
> > > WHERE STATUS_CODE <> 'HOLD' AND STRING1 = 'GROCERY' AND PRODUCT_ID NOT
 IN
> > > (SELECT DISTINCT T1.PRODUCT_ID
> > > FROM PRODUCT T1, PRODUCT T2, PRICING T3
> > > WHERE T1.PRODUCT_ID = T2.PARENT_ID
> > > AND T2.PRODUCT_ID = T3.PRODUCT_ID)
> > >
> > > Thanks if you can help.
> > >
> > > --
> > > To reply directly, replace the zero in my email address with the
 letter e.
> > >
> > >
> > >
> >
> >
> >
>
>
Received on Tue Feb 27 2001 - 20:42:01 CST