Slow update query, looking for suggestions

From: Jason Morris <morrisj_at_cableregina.com>
Date: 2000/04/28
Message-ID: <B8iO4.3203$4JC.9306585@tomcat.sk.sympatico.ca>#1/1

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