Re: Slow update query, looking for suggestions

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/04/28
Message-ID: <8echpd$a7n$1@nnrp1.deja.com>#1/1

In article <B8iO4.3203$4JC.9306585_at_tomcat.sk.sympatico.ca>,   morrisj_at_cableregina.com (Jason Morris) wrote:
> 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
>

since pcode_import has so few records -- we might want to full scan that and apply the changes to the records in cust_address_ml. assuming a UNIQUE index on pcode_import(address_id,lang_id,version) and an INDEX (unique or otherwise) on cust_address_ml(address_id,lang_id,version), we can update the join as such:
ops$tkyte_at_8i> create table cust_address_ml   2 ( post_code int,
  3 address_id int,
  4 lang_id int,
  5 version int
  6 )
  7 /

Table created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> create index cust_address_ml_idx on cust_address_ml ( address_id, lang_id, version );

Index created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> create table pcode_import
  2 ( pcode int,
  3 address_id int,
  4 lang_id int,
  5 version int,
  6 constraint unique_constraint unique( address_id, lang_id, version ) );

Table created.

ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> set autotrace on explain
ops$tkyte_at_8i>
ops$tkyte_at_8i> UPDATE (select /*+ ordered use_nl(a) index
(a,cust_address_ml_idx) */ b.PCODE, a.post_code
  2            FROM cust_address_ml a, PCode_import b
  3           WHERE a.address_id = b.address_id
  4             AND a.lang_id = b.lang_id
  5             AND a.version = b.version)
  6      set post_code = pcode

  7 /

0 rows updated.

Execution Plan

---

   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=165 Card=1 Bytes=104
          )

   1    0   UPDATE OF 'CUST_ADDRESS_ML'
   2    1     NESTED LOOPS (Cost=165 Card=1 Bytes=104)
   3    2       TABLE ACCESS (FULL) OF 'PCODE_IMPORT' (Cost=1 Card=82
          Bytes=4264)

   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'CUST_ADDRESS_ML' (Co
          st=2 Card=82 Bytes=4264)

   5    4         INDEX (RANGE SCAN) OF 'CUST_ADDRESS_ML_IDX' (NON-UNI
          QUE) (Cost=1 Card=82)

i did this on empty tables -- hence the heavy use of hints. You might not have to hint (use explain plan to see what it would do without them).

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

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Fri Apr 28 2000 - 00:00:00 CDT