Re: Why my index is not used?

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Wed, 17 Jan 2007 16:14:08 +0100
Message-ID: <516splF1ithk4U2@mid.individual.net>

On 17.01.2007 16:10, Robert Klemme wrote:
> On 17.01.2007 15:43, Ana Ribeiro wrote:

>> Please explain me one more thing: even with no predicates which can use
>> an index on the tables Step and Script now we can see that indexes are
>> being used ... Do you understand why?

>
> The generic explanation is that Oracle will sometimes use the index if
> it can satisfy the query from the index alone, i.e.,
>
> create table foo ( c1 varchar2(10), c2 varchar2(10) )
> create index idx_foo on foo ( c1, c2 )
>
> select c2
> from foo
> where c1 = 'bar'
>
> Oracle does not need to go to the table to retrieve the value(s) of c2.

Sorry, forgot to mention the main point: index access can be faster even if there is no criterion because the index might be smaller than the table. So assuming the table above has a lot more columns even

select c1
from foo

might actually be serviced faster from the index because the index is smaller.

Kind regards

        robert Received on Wed Jan 17 2007 - 09:14:08 CST