Re: Help needed with slow update

On Tue, 27 Feb 2001 11:20:58 -0800, "joebob" <joebob_at_zipcon.n0t> wrote:

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

suggestions:
the distinct in your subquery is redundant, as everything behind the in constitutes a set and a set is unique *by relational theory* I would try to use the following hints in the subquery (assuming cost based optimizer)
/*+hash_aj(t1) ordered */
if this doesn't work try rewriting not in as not exists with a correlated subquery.
Otherwise post explain plan results and I will give it another shot tomorrow.

Hth,

Sybrand Bakker, Oracle DBA Received on Tue Feb 27 2001 - 14:58:08 CST