Re: Slow update query, looking for suggestions

Try reversing your predicates
(so b.<whatever> = a.<whatever>)
In the past this was an issue sometimes.

Hth,

Sybrand Bakker, Oracle DBA

Jason Morris <morrisj_at_cableregina.com> schreef in berichtnieuws B8iO4.3203$4JC.9306585_at_tomcat.sk.sympatico.ca...
> This one's got us baffled. Any help?
>
> Oracle 8.0.4.4 on NT 4
>
> The query:
> UPDATE cust_address_ml a
> SET post_code = (select PCODE
> FROM PCode_import b
> WHERE a.address_id = b.address_id
> AND a.lang_id = b.lang_id
> AND a.version = b.version)
> WHERE EXISTS (SELECT pcode FROM PCode_import c
> WHERE a.address_id = c.address_id
> AND a.Lang_id = c.lang_id
> AND a.version = c.version
> )
>
> The tables:
> cust_address_ml has ~240,000 records
> pcode_import has ~9300
>
> pcode_import pk is address_id, lang_id, version
>
> The execution plan looks like this:
> update of cust_address_ml
> filter
> table access (full) of cust_address_ml
> index (full scan) of sys_c008469 (unique)
> index (full scan) of sys_c008469 (unique)
>
> We've let it run for over two hours without result. Any suggestions? Why
> would it do full scans of the index rather than a unique scan? Or is it
> something else that I'm missing?
>
> Thanks
> Jason
> morrisj_at_cableregina.com
>
Received on Fri Apr 28 2000 - 00:00:00 CDT