Re: Why isn't Oracle Using My Index

At 10:49 AM 12/21/2006, William Wagman wrote:
>Greetings,
>
>This is a question I have been looking at and puzzling over for a couple
>of days and am unable to explain, I'm hoping someone can help me
>understand what is going on. In a 9i database I have a table with 41550
>rows on which stistics are generated weekly. In looking at a simple
>select the query does not use an index and I am unable to figure out how
>to make it use the index.
>
>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.

You say c1 is unique. Oracle and the index stats don't seem to know that. How can you have avg_leaf_blocks_per_key > 1 for an index on a unique column?

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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