Re: MVCC and index-only read

Jonah H. Harris wrote on 18.11.2008 20:58:
> On Tue, Nov 18, 2008 at 2:33 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:
>> 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.
>
> No, it doesn't always prefer index fast full scan.

Hmm. I was not talking about an index _fast full_ scan, I was talking about
index scans in general. Personally I have never seen Oracle using a table scan
(whatever kind) if all columns in the select are present in the index.

And the manual actually suggests the same:

"If the statement accesses only columns of the index, then Oracle reads the
indexed column values directly from the index, rather than from the table"
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i52300

>> 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)
>
> Those are essentially clustered indexes, and they're not quite stored
> exactly the same..
>
Hmm, my understanding of a clustered index, that it "orders" the table data
according to the index, but there is still "table data" and "index data", right?

That is a bit different to an index-organized table were only a B-Tree index
exists. This is not mandatory, but for my example (a link table with two PK
columns) only a B-Tree index is created.

(I have to admit I don't really know the concept of clustered indexes)

Thomas