Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help: Limiting the number of rows returned.
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