Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!feed.news.tiscali.de!newsfeed01.sul.t-online.de!newsfeed00.sul.t-online.de!t-online.de!frankfurt2.telia.de!news.otenet.gr!news.grnet.gr!news.ntua.gr!not-for-mail From: "agis" Newsgroups: comp.databases.oracle.server Subject: Re: UPDATE Date: Wed, 2 Feb 2005 14:55:04 +0200 Organization: National Technical University of Athens, Greece Lines: 57 Message-ID: References: NNTP-Posting-Host: gold.acn.gr X-Trace: ulysses.noc.ntua.gr 1107348905 58271 213.5.41.5 (2 Feb 2005 12:55:05 GMT) X-Complaints-To: usenet@ulysses.noc.ntua.gr NNTP-Posting-Date: Wed, 2 Feb 2005 12:55:05 +0000 (UTC) X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-RFC2646: Format=Flowed; Response Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:234791 There is no index to the update columns I use index to find the appropriate rows "Frank van Bortel" wrote in message news:ctqifj$n00$1@news4.zwoll1.ov.home.nl... > 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> 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