Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Help: Limiting the number of rows returned.

Re: Help: Limiting the number of rows returned.

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Tue, 29 Sep 1998 15:31:37 GMT
Message-ID: <3612f620.7867903@dcsun4.us.oracle.com>


On Tue, 29 Sep 1998 08:43:13 -0500, "Rich W." <rwerning_at_execpc.com> wrote:

>I need limit the number of rows returned on a query, the table could hold
>millions of rows so we definetly do NOT want to return the entire table.
>The catch is, I need to select the last n records that were modified.
>
>When I use rownum, it doesn't order the records properly. That is, if I
>issue a select using rownum, ordering by a date field, it does not get the
>last rows. It retrieves them and then does the order by. I need it to do
>the order by first.
>
>ex: SQL> select ncsn,ncnumber,date_modified from nonconform where rownum <
>20 and date_modified is not
> null order by date_modified desc;
>
>Is there a way to do a select so that I will always get the correct rows?
>So that it orders the records, then retrieves the number of rows that I
>specify?
>
>Oh yes, we're using Oracle 7.3.

Rich,

You can do this with an index and a query hint.

SQL> create index hiredate_idx on emp( hiredate );

Index created.

Now you want to use the hint for access INDEX_DESC.

Then following query will return the last 5 employees hired.

SQL> l
  1 select /*+ INDEX_DESC( emp hiredate_idx ) */ ename, to_char( hiredate, 'dd-mon-yyyy hh24:mi:ss' )   2 from emp
  3 where hiredate > to_date( '01-JAN-0001', 'dd-mon-yyyy' )   4* and rownum < 6
SQL> / ENAME TO_CHAR(HIREDATE,'DD

---------- --------------------
ADAMS      12-jan-1983 00:00:00
SCOTT      09-dec-1982 00:00:00
MILLER     23-jan-1982 00:00:00
FORD       03-dec-1981 00:00:00
JAMES      03-dec-1981 00:00:00

Elapsed: 00:00:00.53

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=5 Bytes=125)    1 0 COUNT (STOPKEY)

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=5 By
          tes=125)

   3    2       INDEX (RANGE SCAN DESCENDING) OF 'HIREDATE_IDX' (NON-U
          NIQUE) (Cost=2 Card=5)


You need the "hiredate > to_date( '01-JAN-0001', 'dd-mon-yyyy' )" so Oracle will use the index hiredate_idx.

chris.

>
>Thanks,
> Rich W.
>
Received on Tue Sep 29 1998 - 10:31:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US