Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!shadooby!accuvax.nwu.edu!tank!ncar!unmvax!pprg.unm.edu!hc!lll-winken!uunet!mcvax!hp4nl!philmds!leo
From: leo@philmds.UUCP (Leo de Wit)
Newsgroups: comp.databases
Subject: Re: How do you...
Message-ID: <996@philmds.UUCP>
Date: 6 Apr 89 11:15:58 GMT
References: <1707@anasaz.UUCP>
Reply-To: leo@philmds.UUCP (Leo de Wit)
Organization: Philips I&E DTS Eindhoven
Lines: 46

In article <1707@anasaz.UUCP> john@anasaz.UUCP (John Moore) writes:
    []
|If the relation is indexed on last name, it should be possible to
|do this, but how do I (in SQL in general, and Informix-Turbo in particular):
|
|(1) Make sure that the set of all tuples matching the query not be sorted
|    before I get the records (i.e. it is prohibitively expensive if the
|    database engine extracts a temporary file of the 10,000
|    names, sorts them, and then presents them to my process).
|(2) Limit the query to the first 20 names - it is also too expensive
|    if I get 10,000 names and just throw away the last 9980 of them.
|(3) Later on, without holding open a cursor, get the NEXT 20 names.

Several people said some sensible things about this, so I'll just add
my 2 cents (no real solution for how to limit the query was presented):

Besides using an index on the attribute to be sorted upon (to avoid
ORDER BY), you can narrow down the number of rows to be retrieved
already in the WHERE clause. If you're opening a cursor to fetch the
rows, the 10,000 rows satisfying the query would have to be identified,
even if you don't actually fetch them (if I'm not mistaken). By
narrowing down the number of rows beforehand you should be able to
avoid even this identifying.

This restriction would go along the lines of:

    select ...
    from ...
    where rownum <= 20
    and ...

(rownum is a pseudo-column that contains the number of the tuple
retrieved.  Oracle has it, but I don't know whether this also is in
ANSI SQL).

The next 20 records are also easy:

    select ...
    from ...
    where rownum between 21 and 40
    and ...

Of course you should use host variables to make this 'portion of rows
selection' variable.

	 Leo.