Jonah H. Harris wrote on 18.11.2008 20:58:
On Tue, Nov 18, 2008 at 2:33 PM, Thomas Kellerer <spam_eater@xxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
