Re: Use index for order by ?
Date: 1996/01/25
Message-ID: <822612017snx_at_kbigate.stgt.sub.org>#1/1
Sameer writes in article <4dkjpu$gt2_at_news.nyc.pipeline.com>:
>
> Is there a way to make Oracle use index on a table for ordering rows ?
> Here is the problem : Say you have a table with million rows, you want to
> show 20 at a time on a screen in forms 4.5 (a very extreme example but
> illustrates what I want to say), you want rows to be ordered by a column
> (say cust_number which is primary key on customer table).
>
> If you create a base table block and use order by clause, it seems Oracle
> attempts to sort all million rows before showing first set of 20 which can
> take awfully long time.
>
> Is there a way of saying use index (primary key) and show first 20 without
> sorting all million (which can be very fast as far as performace seen by
> the use is concerned) ?
>
As always: it depends ...
The use of indexes (at least when you use the rule-based optimizer) is only enforced on the where clause, not on the order by clause. So simply add something like "where column > 0".
Willy Klotz
>
Willys Mail FidoNet 2:2474/117 2:2474/118
willyk_at_kbigate.stgt.sub.org VFC ISDN/X.75
-> No Request from 06.00 to 08.00 <-
Received on Thu Jan 25 1996 - 00:00:00 CET