Re: Why isn't Oracle Using My Index
- From: "Mark Brinsmead" <pythianbrinsmead@xxxxxxxxx>
- To: wjwagman@xxxxxxxxxxx
- Date: Thu, 21 Dec 2006 19:17:37 -0700
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@xxxxxxxxxxx> 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
Other related posts:
