Re: prefixed index - high clustering factor & high cpu

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_at_rsiz.com> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Ujang Jaenudin
> Sent: Wednesday, October 24, 2007 2:18 AM
> To: oracle-l_at_freelists.org
> 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_at_gmail.com> 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>

-- 
regards
ujang
--
http://www.freelists.org/webpage/oracle-l