UPDATE

From: agis <agstamy_at_yahoo.com>
Date: 2 Feb 2005 04:20:02 -0800
Message-ID: <d2aa3279.0502020420.2d6ca46@posting.google.com>

Oracle 8i (8.1.7.4) on AIX 4.3.3

No indexes on the table that is going to be updated

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

The tables has statistics

This run takes about 6hours to update 6 milion rows each time Received on Wed Feb 02 2005 - 06:20:02 CST