--- In IBDI@y..., David Jencks <davidjencks@e...> wrote:
> Hi,
>
> We've had a long discussion here, featuring:
>
> -limit m. n
>
> -bookmarks
>
> -rownums
>
> -Ann's arbitrarily complicated query to fetch the next records
using a
> bookmark
>
> and we found that people want to do this with arbitrarily complex
joins.
> My question is, is there any reason to think the rownum or
complicated
> query solutions won't force the server to fetch all the rows
satisfying the
> query into memory, then skip the first n, or rows not satisfying the
> "nested after" condition, and start returning more?
>
> In other words, don't these all boil down to asking the server to
do all
> the extra work it would have to do with limit m, n anyway? The
syntax for
> limit is certainly simpler and easier to use, doesn't return a fake
column
> ( although that might be ok anyway), and might be easier for the
engine to
> understand.


I think ROWNUM would be redundant in the result set that is returned
to the user, as it is implicit in the actual order of the rows
fetched. It is of course usefull for subselects within the same
query. LIMIT M, N or other variations of it could probably be just
the shortcut to achieve the effect. It is certainly easier to use and
is similar to MySQL and Postgres variants.


>
> In regard to limit m, n, are there any cases when the server could
avoid
> loading all rows into memory, sorting them, then skipping the first
m-1,
> and returning those requested (for instance by using indexes)?
(Answers
> for the current optimizer and a hypothetical perfect one both
welcome).

Alex said something about the current selection scheme which meant it
could not not avoided. I don't think it is possible to avoid this in
a hypothetical scheme either. The server must count the entries
before it knows which ones to skip.


>
> I still think the best answer to this question is to figure out how
to make
> the user ask a question that will only give her 7 rows, perfectly
answering
> their needs, but this is not really a database question.
>
> david jencks
>
> ps.
>
> Ann's query needs to include enough columns in the order by to give
each
> row a unique "bookmark":
>
> suppose the last row in a batch is the 5th in a set of 10 with
identical
> sort column values. Depending on the version of the query,
the "next" set
> will include all 10 or none of these rows.
>
> To generate a working query automatically, I suppose you need to
include
> pk's from all tables in the join in the order by clause, after the
ones you
> are interested in.

I did think about that, as you had mentioned this need in a previous
post:) But in this particular case any valid combination of cinema,
room, time, date would presumably have only one entry in the
schedules table.

Kind Regards

Fred Toussi

>
> On 2001.06.05 14:45:00 -0400 ft@c... wrote:
> > Oops!
> >
> > This type of query won't work. Suppose the last movie in the
first
> > list is a late night screening, the next query will exclude
almost
> > all the movies that the full dataset of the first query would
return.
> >
> > In general, in a dataset sorted on multiple keys, you need a
unique
> > key sorted according to the combined aggregate sorting order in
order
> > to be able to select consecutive parts. ROWNUM is such a key with
the
> > added property of being a counter.
> >
> > Fred Toussi
> >
> > --- In IBDI@y..., "Ann W. Harrison" <aharrison@i...> wrote:
> > > At 04:48 PM 6/5/2001 +0200, Paulo Gaspar wrote:
> > >
> >
> >
> > > My inclination would be to construct an initial query like this:
> > >
> > > select <whatever> from movies m
> > > join schedules s on s.movie = m.movie
> > > join cinemas c on c.cinema = s.cinema
> > > join towns t on t.town = c.town
> > > where 50 <= distance (mycity, city)
> > > and s.date >= CURRENT_DATE and s.date < CURRENT_DATE
+ 7
> > > order by m.movie, t.town, c.cinema, s.room, s.date, s.time
> > >
> > > I'd get the first 20, noticing the values of the last row
retrieved.
> > > When the user hits next, I'd issue this query:
> > >
> > > select <whatever> from movies m
> > > join schedules s on s.movie = m.movie
> > > join cinemas c on c.cinema = s.cinema
> > > join towns t on t.town = c.town
> > > where 50 <= distance (mycity, city)
> > > and s.date >= CURRENT_DATE and s.date < CURRENT_DATE
+ 7
> > > and m.movie >= :saved_movie
> > > and t.town >= :saved_town
> > > and c.cinema >= :saved_cinema
> > > and s.room >= :saved_room
> > > and s.date >= :saved_date
> > > and s.time >= :saved_time
> > > order by m.movie, t.town, c.cinema, s.room, s.date, s.time
> > >
> > > I'd get 21 of these, throw out the first one as a duplicate, and
> > > return those values. Prior is left as an exercise for the
student.
> > >
> > > If I offered the user the ability to click on an arbitrary page
> > > rather than just go through hitting next and prior, I'd retrieve
> > > enough data to fill the range of pages I offered (as google
> > > appears to do) and get more if the user showed signs of moving
> > > out of that range.
> > >
> > > Actually, and in fact, I'd write an intermediate application
> > > designed to cache significant quantities of data. This is a
> > > place where cheap memory is a real win.
> >
> >
> >
> > Community email addresses:
> > Post message: IBDI@y...
> > Subscribe: IBDI-subscribe@y...
> > Unsubscribe: IBDI-unsubscribe@y...
> > List owner: IBDI-owner@y...
> >
> > Shortcut URL to this page:
> > http://www.yahoogroups.com/community/IBDI
> >
> > Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/
> >
> >
> >
> >