Re: force use of an index - how to?

In article <odysscci.323.0015F303_at_teleport.com> odysscci_at_teleport.com (Jim Kennedy) writes:
>Let us say I have a table called person with lastname,firstname,middlename. I
>want a list of everyone but sorted by lastname,firstname,middlename.
>
>thus:
>select * from person order by lastname,firstname,middlename;
>
>Assume I have created an index on lastname,firstname,middlename.
>
>I am using Oracle 7.1.3.3 and It keeps doing a full table scan
>and then sorting the results. Not good. I have added where rownum<1000 and
>that certainly is much faster, but I want the possibility of fetching the
>whole result set. I have tried hints by specifying the index to use and I
>have tried setting things up so I get best response time on first rows.
>Nothing made it any better. Any suggestions besides don't do that.
>
>Jim Kennedy

I don't know if this helps any, but...

ORACLE will always use an index if one exists for the table... UNLESS you perform a function on the indexed column. ie DECODE, TO_NUMBER, TO_CHAR, etc.

See if indexing only the lastname column helps rather than indexing all of your columns.

James Richard (JRICHARD_at_TUNS.CA)
"No Assembler Required!" Received on Wed Apr 26 1995 - 00:00:00 CEST