Re: Large Update .. Please Advice

From: nagaraj <p.sekhar_at_tcs.com>
Date: 31 Jan 2005 15:10:29 -0800
Message-ID: <1107213029.937570.48700@c13g2000cwb.googlegroups.com>

Try with this

UPDATE tvbill_cis_co_cdrs a
SET
(a.orig_called_number,a.release_source,a.h323_voice_qual­ity,a.gtd_term_cic,a.coder_type_rate)

=
(SELECT DECODE(H323CALLTYPE||H323CALLOR­IGIN ,
'TA',ORIGCALLEDNUM,NULL),
RELEASESOURCE, H323VOICEQUALITY, GTDTERMCIC, CODERTYPERATE FROM cisco_updates b
WHERE ROWID BETWEEN 'AAAEeJABwAAAAAJAAA' AND 'AAAEeJABwAAAXQICcQ'

AND 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_remot­e_address,'x') AND ROWNUM<2)
WHERE h323_connect_time>=TO_DATE('01/12/2004','DD/MM/YYYY') AND h323_connect_time<TO_DATE('01/01/2005','DD/MM/YYYY') --AND a.h323_conf_id IN (SELECT h323confid FROM cisco_updates c WHERE ROWID BETWEEN 'AAAEeJABwAAAAAJAAA' AND 'AAAEeJABwAAAXQICcQ') AND EXISTS (SELECT h323confid FROM cisco_updates c WHERE ROWID BETWEEN 'AAAEeJABwAAAAAJAAA' AND 'AAAEeJABwAAAXQICcQ' AND c.h323confid=a.h323_conf_id )
AND a.release_source IS NULL;

First look for explanin plan with index and without index, Basing on the best plan go for one. What is the significan use of rownum in the subquery.

all the best
Raja Sekhar Received on Mon Jan 31 2005 - 17:10:29 CST