prefixed index - high clustering factor & high cpu

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
--

http://www.freelists.org/webpage/oracle-l Received on Wed Oct 24 2007 - 01:11:54 CDT