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

Hi Jaap,

Note in my little piece of code previously in this thread I used an inline view to answer the poster's original question. Just a couple of things to highlight about that code which may answer some of your questions:

1. The inline view explicitly uses an order by so I'm *guaranteeing* the data from the inline view will be returned in my required order (the capability to have an order by in a subquery was only introduced in 8i)
2. The rownum < x line in the outer query will *definitely* retrieve the first x rows, in the order I specified in my inline views.
3. And this is the nice bit. Oracle *knows* when it performs the inline view I only want 10 rows (by checking the outer query predicate). Therefore as it ploughs through the data in the inline view, it actually only keeps track of the 10 rows that have the lowest values. This means that the sort it actually performs in the inline view is trivial. it doesn't have to sort all 1 million rows, only those 10 rows that make 'The Final Cut (my Pink Floyd reference, are you listening Norman :).

So to perform what Oracle describes as top (or bottom) n analysis, the inline view with order by is a very efficient way to go because of the avoidance of the
huge sort and the fact it's guaranteed to provide the data you are asking.

Cheers

Richard
"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> wrote in message news:3d404ab2.829068_at_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 - 20:39:14 CDT