Re: Large Update .. Please Advice

agis wrote:

> Hello to all,
>
> i must do a large update .
>
> I have 2 tables with 37 million records tvbill_cisco_cdrs partition
> (log_2004_12)
> and cisco_updates . I want by selecting from cisco_updates to update
> some columns on tvbilL_cisco_cdrs , i am doing this :
>
> update /*+ index (a CONNECT_TIME_CONF_ID_IDX) */ tvbill_cisco_cdrs a
> set (a.orig_called_number,a.release_source,a.h323_voice_quality,a.gtd_term_cic,a.coder_type_rate)
> =
> (select /*+ FIRST_ROWS */ case when H323CALLTYPE||H323CALLORIGIN =
> 'TA' then ORIGCALLEDNUM else null end,
> RELEASESOURCE, H323VOICEQUALITY, GTDTERMCIC, CODERTYPERATE
> from cisco_updates b
> where rowid between 'AAAEeJABwAAAAAJAAA' and
> 'AAAEeJABwAAAXQICcQ'
> and b.h323confid=a.h323_conf_id
> and b.h323calltype=a.h323_call_type
> and b.H323CALLORIGIN=a.h323_call_origin
> and b.h323connecttime=a.h323_connect_time
> and b.acctoutoctets=a.acct_output_octets
> and nvl(b.h323remoteaddress,'x')=nvl(a.h323_remote_address,'x')
> and rownum<2)
> where h323_connect_time>=to_date('01/12/2004','DD/MM/YYYY')
> and h323_connect_time<to_date('01/01/2005','DD/MM/YYYY')
> and a.h323_conf_id in (select /*+ FIRST_ROWS */ h323confid from
> cisco_updates c where rowid between 'AAAEeJABwAAAAAJAAA' and
> 'AAAEeJABwAAAXQICcQ')
> and a.release_source is null;
>
> and the rowid ranges were found from table split by rowid.
>
> Can anyone suggest an other way because this takes allmost 2 days to
> complete

What version of Oracle?
What hardware and operating system?
What indexes exist?
Are optimizer statistics current?

I can't believe 2 days for such a small table ... and yes I said small table. I can do that on my laptop in a fraction of the time it is taking you.

I'd suggest a little explain plan, a little autotrace, a little TKPROF to find out what's taking all that time.

And why the hint? It is exactly what you don't need. You need the entire job to complete quickly. You get no benefit from the first rows completing fast and the rest of the job dragging on.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)