Re: How to use index

> bcoz I select columns which are not indexed, the index created for
> column 1 cannot used either
not true. the columns in the select list do not affect which indexes the optimizer uses

your IN clause is the equivalent of 3 equality predicates with OR -- the optimizer may very well not use the index if it appears that it would be faster to do a full table scan

do you have statistics on the table?

how many rows are in the table? the 'rule of thumb' is that oracle will use an index if it determines that about 2% of the rows will be returned

--mcs

"Ken" <wingkei_leung_at_hotmail.com> wrote in message news:63029411.0311051846.55f7406e_at_posting.google.com...
> Dear all,
>
> I have a beginner of oracle and recently I have a prob with using
> indexes. I have a table with 5 columns, with no primary key set in the
> table. An index has created for column 1. I have a sql which retrieve
> rows based on the indexed column:
>
> select *
> from my_table
> where
> column_1 in ('A','B','C')
>
> bcoz I select columns which are not indexed, the index created for
> column 1 cannot used either. Pls suggest a way so that I can retrieve
> all columns, at the same time db will make use of my index in column
> 1.
>
> Many many thanks if anyone can kindly help me.
Received on Thu Nov 06 2003 - 04:21:40 CET