Re: Slow update query, looking for suggestions

        It looks as though you don't have a useful index (for this query) on cust address ml. Try adding a single column index on address id and rerun your explain plan.

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 4/28/00, 10:30:41 AM, morrisj_at_cableregina.com (Jason Morris) wrote regarding Slow update query, looking for suggestions:

> 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 Sat Apr 29 2000 - 00:00:00 CDT