[comp.databases] Oracle : Next/Previous row selection

jim@bilpin.UUCP (jim) (02/02/89)

    #{ v_database.1 }


    Oracle V5.1.17 
    Looking for an EFFICIENT solution to selecting NEXT or PREVIOUS row 
    ( with respect to the indexed column(s) being selected upon ) in Pro*C.

    The first step wpuld seem to be opening two cursors, 
    one with 'ORDER BY ... ASC' and the other with 'ORDER BY ... DESC',
    which is fine if you're always reading in the same direction, but falls
    down if you want to periodically change the direction in which you are
    reading through the table, as the pointers in the two active sets get
    out of synch, and you can only re-access a row from earlier in the
    active set by closing and re-opening the cursor and reading back to that
    point, which is going to be unacceptably slow.

    Anydoby solved this, or got any ideas?
-- 
_______________________________________________________________________________
Path	: mcvax!ukc!icdoc!bilpin!jim	*    Being paranoid doesn't mean that
Who	: Jim G, Hatfield, England	*    everybody ISN'T out to get you.
_______________________________________________________________________________

leo@philmds.UUCP (Leo de Wit) (02/16/89)

In article <686@bilpin.UUCP> jim@bilpin.UUCP (jim) writes:
|    Oracle V5.1.17 
|    Looking for an EFFICIENT solution to selecting NEXT or PREVIOUS row 
|    ( with respect to the indexed column(s) being selected upon ) in Pro*C.
|
|    The first step wpuld seem to be opening two cursors, 
|    one with 'ORDER BY ... ASC' and the other with 'ORDER BY ... DESC',
|    which is fine if you're always reading in the same direction, but falls
|    down if you want to periodically change the direction in which you are
|    reading through the table, as the pointers in the two active sets get
|    out of synch, and you can only re-access a row from earlier in the
|    active set by closing and re-opening the cursor and reading back to that
|    point, which is going to be unacceptably slow.
|
|    Anydoby solved this, or got any ideas?

(I always got ideas!).

I can think of some possible solutions:

a) Keep track of the data you already read, by saving it in dynamically
allocated space. Of course this can get easily out of hand with lots of
data; you could alternatively store only the rowids of the rows you
retrieved sofar, preferably in a linked list. Since access by ROWID is
claimed to be the fastest, a separate select by rowid would probably
not be 'unacceptably slow'. If you expect the table to change, access
by ROWID is not safe and you should use a (unique) index instead.
Drawback of this method is that you have to maintain linked lists, do
mallocs etc.

b) If the change in direction is not too often, you can still use two
cursors, just like you said; with a little trick you can avoid reading
back to the point where you left off with the other cursor, and instead
start reading IMMEDIATELY at that point (of course you'll still have to
close one cursor and open another). To make this clear, I have a very
simple table:

create table demo (num number);

create unique index idemo on demo(num);

It is filled with numbers 1 through 100.

Now suppose we start reading this table using a cursor, ordered by num
asc.  When we hit 40, we decide for some reason to read backwards. Easy
enough, if our 'backward cursor' looks like:

    EXEC SQL
        DECLARE CURSOR backnum AS
            SELECT num
            FROM demo
            WHERE num = :curnum
            OR ROWNUM > 1
            ORDER BY num DESC;

(curnum is a host variable that will contain the value 40, or more
generally, the most recent read value of num). When you now open this
cursor, you start reading exactly where you left off going upward. The
SQL statement may look a bit cryptic at first sight, but the WHERE
clause guarantees that your first row (ROWNUM == 1) will have num == 40.
The forward cursor looks almost the same:

      EXEC SQL
        DECLARE CURSOR fornum AS
            SELECT num
            FROM demo
            WHERE num = :curnum
            OR ROWNUM > 1
            ORDER BY num ASC;

Both cursors should of course read num into the host variable curnum.
It goes without saying that you can use the ROWID instead of an indexed
set of attributes, like in proposal a).

Furthermore, you'll need some way to initialize curnum to an existing
value (or perhaps the beginning or end of the ordered series).  This
will do the trick (selecting exactly one value, but not necessarily at
one of the ends); you can alternatively use min(), max() (although this
is less general: think of multiple indices).

      EXEC SQL
        SELECT num
        INTO :curnum
        FROM demo
        WHERE rownum == 1;

Hope this might solve your problem -
                                       Leo.