Re: Why isn't Oracle Using My Index

Bill,

   There's been a lot of great feedback here already. Maybe I've missed something, but we could maybe try a slightly more pragmatic line of attack...

   How about

CREATE INDEX FOO ON T185 (C1, C240000008);    With this index, the clustering factor will be irrelevant. The query can be resolved entirely from the index now, with probably a small handful of LIOs...

   Of course, this won't be so pragmatic if your real query selects a bunch more columns than the (maybe simplified -- perhaps that is what I have missed) example you provided...

On 12/21/06, William Wagman <wjwagman_at_ucdavis.edu> 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 /
>
> ...

-- 
Cheers,
-- Mark Brinsmead
   Senior DBA,
   The Pythian Group
   http://www.pythian.com/blogs

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