Re: Slow update query, looking for suggestions

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