Re: Help needed with slow update

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 - 14:36:31 CST