Re: 9.2 and index only scans

Tom Lane, 28.08.2012 16:30:
>> In Oracle an index (entry) has the information about transactional visibility.
>
> You sure about that?

Yes, although technically it's not the index *entry*, but the index *block*.
But the result is the same thing.

The visibility information is stored on data block level. And an index block is not really different to a table block when it comes to transaction handling.

> What I always understood about Oracle is that the main table (and by
> implication, also the indexes) has *only* the most up-to-date version
> of any row.

Yes that is true. The block stores a pointer to the "old" versions (through the so called SCN - comparable somehow to PG's txid).

Depending on the transaction reading the block it will either follow that link (if it's a different transaction) or use that block (if it's the transaction that modified the block in the first place).

This is somewhat simplified, but I think it's a good enough picture.

If you are interested in more details, see the link to the concepts manual I posted in the response to Craig

Thomas