Re: Large Update .. Please Advice

From: agis <stag_at_hq.acn.gr>
Date: Wed, 2 Feb 2005 14:52:49 +0200
Message-ID: <ctqij9$1osv$1@ulysses.noc.ntua.gr>

I have Oracle 8iR3 (8.1.7.4), the columns updated are not indexed OS : AIX 4.3.3 , RAID 5 I changed my query to this :

update /*+ index (a CONNECT_TIME_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 case when H323CALLTYPE||H323CALLORIGIN = 'TA' then ORIGCALLEDNUM else null end,
RELEASESOURCE, H323VOICEQUALITY, GTDTERMCIC, CODERTYPERATE from cisco_updates b

           where 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 a.h323_connect_time>=to_date('20/12/2004','DD/MM/YYYY') and a.h323_connect_time<to_date('25/12/2004','DD/MM/YYYY') and a.release_source is null

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

UPDATE STATEMENT Optimizer Mode=CHOOSE 292 K 2869375   UPDATE BILLING_MASTER.TVBILL_CISCO_CDRS     TABLE ACCESS BY LOCAL INDEX ROWID BILLING_MASTER.TVBILL_CISCO_CDRS 292 K

31 M 2869375                        6 6
      INDEX RANGE SCAN BILLING_MASTER.CONNECT_TIME_IDX 292 K   13920 

6 6
  COUNT STOPKEY
    TABLE ACCESS BY INDEX ROWID BILLING_MASTER.CISCO_UPDATES 1 85 6       INDEX RANGE SCAN BILLING_MASTER.H323_IDX 1 4 I updates in about 5h 30min 6 milion rows

"agis" <agstamy_at_yahoo.com> wrote in message news:d2aa3279.0501310344.66fdd550_at_posting.google.com...
> 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
Received on Wed Feb 02 2005 - 06:52:49 CST