Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!not-for-mail
From: agstamy@yahoo.com (agis)
Newsgroups: comp.databases.oracle.server
Subject: UPDATE
Date: 2 Feb 2005 04:20:02 -0800
Organization: http://groups.google.com
Lines: 42
Message-ID: <d2aa3279.0502020420.2d6ca46@posting.google.com>
NNTP-Posting-Host: 213.5.41.5
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1107346803 18395 127.0.0.1 (2 Feb 2005 12:20:03 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 2 Feb 2005 12:20:03 +0000 (UTC)
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:234787

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