Re: indexing "select" columns

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<tho1ugefvkpq45_at_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

Hi Sybrand,

 select col2, col1
 from table1
 where col1= value

is performing with the same speed using index1 and index12, so your theory does not work.

for count(*) index1 is working faster as soon as index smaller. But we are not going to go record count we are going to use some data like company_name as col2.

Thanks
Leon Received on Tue Jun 05 2001 - 11:55:53 CDT