Re: Use index for order by ?
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