Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!news.tele.dk!news.tele.dk!small.news.tele.dk!tiscali!newsfeed1.ip.tiscali.net!border2.nntp.ams.giganews.com!border1.nntp.ams.giganews.com!nntp.giganews.com!newsfeeder.concepts.nl!213.51.141.6.MISMATCH!newshub3.home.nl!newshub1.home.nl!home.nl!not-for-mail
From: Frank van Bortel <fvanbortel@netscape.net>
Newsgroups: comp.databases.oracle.server
Subject: Re: UPDATE
Date: Wed, 02 Feb 2005 13:54:43 +0100
Organization: @Home Benelux
Lines: 50
Message-ID: <ctqifj$n00$1@news4.zwoll1.ov.home.nl>
References: <d2aa3279.0502020420.2d6ca46@posting.google.com>
Reply-To:  fvanbortel@netscape.net
NNTP-Posting-Host: cc28855-a.hnglo1.ov.home.nl
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: news4.zwoll1.ov.home.nl 1107348787 23552 212.204.168.125 (2 Feb 2005 12:53:07 GMT)
X-Complaints-To: usenet@corp.home.nl
NNTP-Posting-Date: Wed, 2 Feb 2005 12:53:07 +0000 (UTC)
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
In-Reply-To: <d2aa3279.0502020420.2d6ca46@posting.google.com>
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:234790

agis wrote:
> 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
Is there a question in this?

I have one: why hint to use an index, while you claim there is no index?

-- 
Regards,
Frank van Bortel