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.