Re: Slow update query, looking for suggestions

In the original update you are doing it seems like you are adding an unnecessary where clause. Typically it is good to add where clauses since it speeds up a query. In this case, however, you are doing a subquery which, in effect, is the same thing as joining to another table. If cust_address_ml.post_code is initially null then you can just drop the where clause completely. If not then I think you can do the following with nvl(I don't have access to oracle now):

UPDATE cust_address_ml a
SET post_code = nvl((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),
                     post_code)

-Peter

On Fri, 28 Apr 2000, Jason Morris wrote:

> Just one problem, I can't do a joined update in Oracle 8.0.4....
>
>
> In article <8echpd$a7n$1_at_nnrp1.deja.com>, tkyte_at_us.oracle.com says...
> >
> >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