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

Home -> Community -> Usenet -> c.d.o.server -> Re: how to fetch the first n rows only for a query

Re: how to fetch the first n rows only for a query

From: Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl>
Date: Thu, 25 Jul 2002 19:07:21 GMT
Message-ID: <3d404ab2.829068@news.jaapwvandijk.myweb.nl>


Will the inline view retrieve all the rows first? In that case, assuming xyz is much smaller than the total number of rows and assuming there is an index on blah, the following could be faster:

select /*+ index_desc (tabname indname) */ abc from blah where rownum <= xyz

A bit tricky though because if something about the index changes (existence, name, columns) the query would give the wrong result.

Jaap.

On Thu, 25 Jul 2002 17:43:55 +0100, "Telemachus" <telemachus_at_ulysseswillreturn.net> wrote:

>Yes but as someone else pointed out use an inline query to get what you want
>
>
>select abc from ( select <your query > order by blah ) where rownum < xyz
>
>and yes, I agree, but when would it be important to retrieve this
>information in a relational framework ?
>
>and I would regard the first record as the one with the earliest timestamp
>if there was one and so on...
>"TurkBear" <jgreco1_at_mn.rr.com> wrote in message
>news:l060ku0bbqfg6p9mjorqr4c8kcngd8a2v1_at_4ax.com...
>>
>> One additional nitpick.
>> .The rows returned by a where rownum < 25 ( or whatever)
>> are, by no means, the FIRST 25 records in the database - The concept of
>first or last ( or second or whatever number)
>> is meaningless in a RDBMS - the 'order' in which rows are stored in not
>determinate and may change ( probably will if many
>> changes to the table are made)
>> The rownum < 25 just limits the number or rows returned, nothing else.
>>
>> My 2c on one of my pet peeves....
>>
>>
>> Fan Ruo Xin <fanruox_at_yahoo.com> wrote:
>>
>> >Thank you, Howard!
>> >I did think they are the same thing. Thanks for your correcting me.
>> >BTW, I am jsut curious when ORACLE introduce rownum?
>> >Regards,
>> >FRX
>> >
>> >"Howard J. Rogers" wrote:
>> >
>> >> Don't get too carried away... ROWID is not the same thing as ROWNUM at
>all,
>> >> and ROWID is still bad to use in a relational database, because you
>have no
>> >> control whatsoever over what rows get which rowids. And once assigned a
>> >> rowid, a row always retains that same rowid (with one or two exceptions
>> >> introduced in Oracle 8i). So a test on rowid<x.x.x.x would always
>return
>> >> exactly the same set of rows, regardless of what order by clause you
>put in,
>> >> or what changes you make to the data.
>> >>
>> >> But ROWNUM is assigned once a row has been selected. And that's why the
>> >> 'ROWNUM<x' thing works.
>> >>
>> >> Regards
>> >> HJR
>> >>
>> >> "Fan Ruo Xin" <fanruox_at_yahoo.com> wrote in message
>> >> news:3D3E07EE.72CC7B8D_at_yahoo.com...
>> >> > Thanks for the response from you all!!!
>> >> > I always forget "rowid/rownum". I used to think "rowid" is not a good
>idea
>> >> > when you use SQL for relational DBMS. Now I chang my thinking.
>> >> > Regards,
>> >> >
>> >> >
>> >> > Fan Ruo Xin wrote:
>> >> >
>> >> > > Hi,
>> >> > > I have a very simple query like,
>> >> > > SELECT ...
>> >> > > FROM MyTable
>> >> > > WHERE
>> >> > > ORDER BY
>> >> > >
>> >> > > Both the tablesize of mytable and result size are very big. I only
>care
>> >> > > the first 10 or 20 rows from the result set. How to implement it in
>> >> > > ORACLE?
>> >> > > BTW, I searched SELECT stmt in SQL Reference, got no help.
>> >> > >
>> >> > > Regards,
>> >> > > Fan Ruo Xin
>> >> >
>>
>>
>>
>> -----------== Posted via Newsfeed.Com - Uncensored Usenet News
>==----------
>> http://www.newsfeed.com The #1 Newsgroup Service in the World!
>> -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers
>=-----
>
>
Received on Thu Jul 25 2002 - 14:07:21 CDT

Original text of this message

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