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

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: wjwagman@xxxxxxxxxxx
  • Date: Thu, 21 Dec 2006 12:17:01 -0700

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

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

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

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.


Wolfgang Breitling
Centrex Consulting Corporation


Other related posts: