Jonah H. Harris wrote on 18.11.2008 20:15:
> On Tue, Nov 18, 2008 at 2:02 PM, Scara Maccai <m_lists@yahoo.it> wrote:
>> SELECT A FROM myTAB where A <10000
>>
>> only uses the index (if there's an index defined for A) in Oracle.
>
> Well, not exactly. That's called a "covered" index because the query
> could be satisfied directly from the index (the attribute is covered
> by the index). Oracle sometimes satisfies it with an index fast full
> scan, but not always; it depends on the cost of other access methods
> and/or what Oracle believes is currently in cache.
>
If all the columns from the select list are available in the index, then Oracle
will always prefer the index scan over a table scan (at least I have never seen
something else). Even for a SELECT that returns all rows of the table.
They are taking this concept even further with index organized tables, where no
real "table data" exists, everything is stored in the index (quited nice for
e.g. link tables that only consist of two or three integer columns)
Thomas