There is no index to the update columns
I use index to find the appropriate rows
"Frank van Bortel" <fvanbortel_at_netscape.net> wrote in message
news:ctqifj$n00$1_at_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<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
Received on Wed Feb 02 2005 - 06:55:04 CST
