Re: force use of an index - how to?

odysscci_at_teleport.com writes in article <odysscci.323.0015F303_at_teleport.com>:
>
> 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
>

Try using a where-clause like "where lastname > ' '.

Index use is driven by he where clause, not by the order clause.

Regards

Willy Klotz

Willys Mail     FidoNet              2:2474/117  2:2474/118    
      willyk_at_kbigate.stgt.sub.org      VFC        ISDN/X.75