mark, upsss sorry, the update is here: UPDATE c_transaction SET pgi_status = decode(c_status,0,1,1,1,2,1,3,3), ne_status = 1, messagetracking_id = NULL, last_processing_dtm = SYSDATE WHERE transc_id = :1 and e_transc_id = :2 select * from c_transaction where pg_transc_id is null; no rows returned. 811120980.20070920033742 811120980.20071021183003 it seems customer number and its transaction (time) id and the concatenation will form unique entries. i think the b-tree index itself has limitation when values has prefixed. does anyone knows what is limitation? regards ujang On 10/26/07, Mark W. Farnham <mwf@xxxxxxxx> wrote: > First, I'm curious - what is the purpose of using two different bind > variables as filters to the same column? Are my eyes fooling me or are both > :1 and :2 required to be equal to transc_id? (And therefore equal to each > other, so why use both? > > Next, are you saying that all of these values pretty much start with > > 811120980.2007? (That's lookin' like a YYYYMMDDHH24MISS after the . to me. > Is it? If so, then what is the 81120980 part? Does it vary?) > > Anyway, this looks like a composite key stored in a single column. Maybe you > can't change that at this time, but it is usually a bad idea. > > Now you say this is a unique index, so it must be nullable since you've got > more rows than distinct keys. That shouldn't hurt an update looking for a > specific row. You've got a little over 151 keys per block, but you should > only have to look in one leaf block since it is unique. > > What does the plan look like? Is the CBO somehow being tricked out of using > that index because it is used twice? > > If you're not getting an index lookup I suggest you Wolfgang that query > (10053 trace). Or if I'm right and there just is no purpose to using the > same column in an and with two bind variables, then just repair the query > and see if it speeds things up. > > I'd suggest where (:1 = :2) and transc_id = :1 or something like that if you > can't short circuit the application to not even go to the database when :1 > is different from :2. > > Regards, > > mwf > > > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] > On Behalf Of Ujang Jaenudin > Sent: Wednesday, October 24, 2007 2:18 AM > To: oracle-l@xxxxxxxxxxxxx > Subject: Re: prefixed index - high clustering factor & high cpu > > sorry i forgot to tell you about > > the wait event of cbc (cache buffers chains) is within the query also. > > regards > ujang > > > On 10/24/07, Ujang Jaenudin <ujang.jaenudin@xxxxxxxxx> wrote: > > all, > > > > I have varchar2 column (transc_id) with the value something like > > this...and this column was indexed (unique index) > > > > 811120980.20070920033742 > > 811120980.20071021183003 > > > > > UPDATE c_transaction SET pgi_status = > > decode(c_status,0,1,1,1,2,1,3,3), ne_status = 1, messagetracking_id = > > NULL, last_processing_dtm = SYSDATE WHERE transc_id = :1 and transc_id > > = :2 > > > > at the tkprof the cpu cost is very high , its about 30k, rows returned > > is 1 , io cost is 4 , the cost itself only 4. > > > > the dba_indexes says: > > num_rows = 2195991 > > distinct_key = 1507195 > > clustering_factor = 1476573 > > blevel = 2 > > leaf_blocks = 14486 > > > > histogram is already there with 254 bucket.... > > > > I'm guessing to turn index to reverse key, is it has the benefit impact? > > > > thanks in advance > > > > -- > > regards > > ujang > > > > > -- > regards > ujang > -- > //www.freelists.org/webpage/oracle-l > > > > > -- regards ujang -- //www.freelists.org/webpage/oracle-l