Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Sorting Large table, small select set
Hi,
If you have a index on order by columns, Oracle will choose a full index scan
instead of
full table scan. It can save you lots of time.
Hth
bjin
Mark Foley wrote:
> I have a database for which a certain recordset contains 20,000 records. I
> am
> displaying 20 or so records at a time to the user. These records need to be
> displayed
> in a sorted manner (ie ORDER by DATE,ACCTNUM).
>
> I had been using something like SELECT X From Y Where Rownum < 20 ORDER by
> DATE,ACCTNUM;
>
> only to realize that the ORDER by only works on the 20 rows.
>
> What I really want is to be able to return the 1st 20 records, then the next
> 20 records and so on.
>
> The only way I can come up with to do this is to read the entire recordset
> of 20,000 records and
> scroll through and pick up the ones I want.
>
> This can't be the optimum way to do this. Isn't there some way to enforce a
> sorting or order on the
> table either when selecting records, or perhaps even when initially
> inserting the records? This seems
> like something that would be required in many applications.
>
> I am reading records through ADO/Active Server pages and displaying them to
> the user through
> the browser interface.
>
> Any comments appreciated.
Received on Fri Mar 12 1999 - 00:49:41 CST