Re: Slow update query, looking for suggestions

I'd like to clarify that I can't read, and what was suggested worked very well.  Thanks for all the work and the very quick reply!

Jason

In article <vdkO4.3213$4JC.5440088_at_tomcat.sk.sympatico.ca>, morrisj_at_cableregina.com says...
>
>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