Re: Use index for order by ?

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1996/01/19
Message-ID: <4doo94$s6o_at_inet-nntp-gw-1.us.oracle.com>#1/1


Say you have the table created:

create table customer
( cust_number number,

   .....,
   constraint customer_pk primary key(cust_number) )
/

Then, you can usually just add a default where_clause like:

"where cust_number > 0"

To make "select * from customer where cust_number > 0 order by cust_number" Use the index to access the rows.

If that does not work:

"select /*+ index(customer customer_pk) */ * from customer where cust_number ...."

will do it.
Sameer Utrankar <utrankar_at_nyc.pipeline.com> wrote:

>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) ?

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Fri Jan 19 1996 - 00:00:00 CET

Original text of this message