Re: MVCC and index-only read

On Tue, Nov 18, 2008 at 3:54 PM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
> 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

The manual is wrong.

>> 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.

Well, clustered indexes mean different things to different vendors.
Oracle's implementation stores the data with the index as does SQL
Server, but in a little different fashion.

--
Jonah H. Harris, Senior DBA
myYearbook.com