Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!ncar!noao!asuvax!anasaz!john
From: john@anasaz.UUCP (John Moore)
Newsgroups: comp.databases
Subject: How do you...
Message-ID: <1707@anasaz.UUCP>
Date: 26 Mar 89 15:42:03 GMT
Organization: Anasazi Inc., Phoenix AZ
Lines: 34

Context: Relational Database in Transaction Processing (high performance)

I have a relation that consists of a large (>500,000) set of last and
first names, and an application that needs to do partial searches of
the list. For example, the query might be to find names starting with
"mo". A further restriction is that it must be in alphabetical order.
Finally (and the only tough one for me) is that I only want the first
20 names (for display on a screen). There may be 10,000 names that
meet the criteria. Also, the list is dynamic - names are continuously
being added and deleted by multiple processes.

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.

I should note that this will be implemented in SQL embedded in C.
I am interested in this at two levels: in theory (does SQL itself
and relational theory provide an answer to this), and in the
particular case of Informix-Turbo with C embedded SQL.

Thanks in advance. This is an important question to me and help is
gratefully accepted.
-- 
John Moore (NJ7E)           mcdphx!anasaz!john asuvax!anasaz!john
(602) 861-7607 (day or eve) 
The opinions expressed here are obviously not mine, so they must be
someone else's. :-)