Re: Limiting the resultset returned from a SELECT to the first N rows?

From: <syakobson_at_erols.com>
Date: 1997/10/28
Message-ID: <878090427.6198@dejanews.com>#1/1

There are several ways, but all of them are based on forcing Oracle to sort by amount instead of using ORDER BY:

Solution I 1. Create an index on amount column 2. Force Oracle to use it by adding an INDEX hint and WHERE amount < 10 power n, where n is number of digits in column amount definition. 3. Index is sorted, so there is no need to ORDER BY. Therefore we can use ROWNUM and select first 10 rows.

   SELECT /*+ INDEX(tbl,tbl_ind1) */

                 amount
     FROM  tbl
     WHERE amount < 1000000
        AND  ROWNUM < 11;

assuming amount is defined as NUMBER(6). This solution will not work as expexted if you have duplicates but want to treat them as one occurence.

Solution II

SELECT amount
  FROM (SELECT DISTINCT amount

                  FROM tbl
              )

  WHERE ROWNUM < 11;

DISTINCT is implemented via sort, therefore result set is sorted by amount. This solution will not work as expexted if you have duplicates but want to treat them as separate occurences. You do not need index on amount, but having one will improve performance.

Solution III

SELECT amount
  FROM (SELECT amount

                  FROM  tbl
                  GROUP BY amount
              )

  WHERE ROWNUM < 11;

GROUP BY is implemented via sort, therefore result set is sorted by amount. This solution will not work as expexted if you have duplicates but want to treat them as separate occurences. You do not need index on amount, but having one will improve performance.

Please note that all these solutions are based on current implementations of indexes, DISTINCT and ORDER BY via sort. Even though sort is the most logical way to implement these features, there is no guarantee that future Oracle releases will continue to use sort for such implementations.

Solomon Yakobson.

In article <01bce3d3$95d7e550$0201dede_at_mum>,   "Michael G. Schneider" <mgs_software_at_compuserve.com> wrote:
>
> It seems as if ROWNUM would be calculated before a sort is done. So a ...
>
> SELECT * FROM tbl WHERE ROWNUM<10 ORDER BY amount
>
> does not return the 10 rows with the smallest amounts.
>
> Is there some other possibility than ROWNUM?
>
> Michael G. Schneider
>
> mgs_software_at_compuserve.com
>
> > > I would like to write a SELECT statement and limit the number of
 returned
> > > rows. Something like
> > >
> > > SELECT * FROM tbl ORDER BY amount TOP 10
> > >
> > > Is this possible?
> > >
 

> > SELECT * FROM tbl ORDER BY amount WHERE ROWNUM<10

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Tue Oct 28 1997 - 00:00:00 CST