Re: FW: Why isn't Oracle Using My Index

At 11:58 AM 12/21/2006, William Wagman wrote:
I made an error initially when I sent the information re the clustering
factor, here is the correct query...
TABLE_NAME                         BLEVEL AVG_DATA_BLOCKS_PER_KEY
------------------------------ ---------- -----------------------
AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR
----------------------- -----------------
T185                                    1                       1
                      1             10276

SQL> set autotrace traceonly explain;
SQL> SELECT C240000008 FROM aradmin.t185 WHERE C1 = 'HD0000000041608'
  2  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1420 Card=413 Bytes=
          627760)

   1    0   TABLE ACCESS (FULL) OF 'T185' (Cost=1420 Card=413 Bytes=62
          7760)

There is an index IT185 on column C1 and column C1 is unique. A hint
will force the use of the index but in that this is not a locally
developed application I am unable to change the code. Nevertheless, in
attempting to understand this I looked at the clustering factor for the
index.

Is the index declared as unique? Why would Oracle assume that 413 rows qualify for an equality predicate on a unique column??
what do the column statistics (num_distinct, density, buckets) say for c1.


Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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


Other related posts: