Re: Sorting Large table, small select set

From: <afilonov_at_pro-ns.net>
Date: Sat, 13 Mar 1999 03:24:31 GMT
Message-ID: <7cclp8$kt8$1@nnrp1.dejanews.com>

select x
from y a
where 20 > (select count(*)

            from y b
	    where b.ydate >= a.ydate)

order by ydate desc, acctnm

If you have same values in ydate, you may have more them 20 rows. In Forms you can restrict amount of rows shown. This query works fast if you have index on ydate. With 7.3 and 8.0 cost-based optimizer gives you very fast result. If you need accending order, replace b.ydate >= a.ydate with b.ydate <= a.ydate.

In article <36E8B905.246389B6_at_hpsgns1.sgp.hp.com>,   bjin <jinbo_at_hpsgns1.sgp.hp.com> wrote:
> 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.
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Mar 12 1999 - 21:24:31 CST