Re: UPDATE

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