Re: Selecting data using index

In article
<7lngd7$nof$1_at_bgtnsc01.worldnet.att.net>, "Juan Carlos" <jc_va_at_hotmail.com> wrote:
>I believe I heard that when you "select ssn from employees" and ssn is
>indexed, that oracle may use the index, if the column(s) are contained in
>the index. Further, I know you can give a hint to "suggest" it use the
>index. But it doesn't sound like a good idea to write production code
>relying on this because someone can change the optimizer (delete stats) or
>drop the index. Is there a way to "force" oracle to use the index, or
>return an error if it can't for some reason? Thanks.
>
>

Your real goal should be to get the fastest possible performance. Using an index may not be the best way to do it.

Retrieval by getting row locations form an index and then getting the rows themselves becomes less efficient than scanning a table if you are going to retrieve more than about 20% of the rows. Since you don't mention a WHERE clause it looks like you want all of the rows.

If you only want the SSN column, and it's the only column in the index, and you think an index-only operation would be faster than a table scan, the dummy WHERE clause should do the trick; if not, try an INDEX hint. But time it both ways before you implement it.

Paul de Anguera | "You can't write a chord ugly enough to say
Reply to:       | what you want to say sometimes, so you have to
deanguer@       | rely on a giraffe filled with whipped cream."
quidnunc.net    | - Frank Zappa