Re: indexing "select" columns

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 5 Jun 2001 00:14:37 +0200
Message-ID: <tho1ugefvkpq45@beta-news.demon.nl>

"Leon" <lrzhemov_at_home.com> wrote in message news:8cb139cf.0106041232.192801d6_at_posting.google.com...
> I hope Oracle would use only index to select information from the
> table as soon as all information is present in an index, so
> select col2
> from table1
> where col1= value
> would use only index as soon as index
> CREATE UNIQUE INDEX12 ON table1(COL1, COL2)
> exists
> Unfortunately statistics for select like that shows that speed of this
> query is exactly the same as with index "CREATE UNIQUE INDEX1 ON
> table1(COL1)"
>
> Does anybody has good experience in using "index only" (simulating
> IOT) queries?
> Potentially it should be faster then IOT as soon as indexes could be
> cached in memory but IOT can't.
>
> Thanks
> Leon

Your example is not going to work.
To have an index only query, the columns referred to must be *all* present in the select list, or there shouldn't be any column at all (like 'x', 1, or count(*))

Please try it.

Hth,

Sybrand Bakker, Oracle DBA Received on Mon Jun 04 2001 - 17:14:37 CDT