Re: Get a block of rows

Ralf wrote:
> Hi to all,
>
> my problem is the following:
> I have a table: "adresses" (ID, type, firstname, lastname).
> There's a primary key on ID and an index on (type, lastname).
>
> I want to get all addresses "where type like 'customer' order by
> lastname"
> But I need a possibility to select only a block of
> customer-addresses (e.g. 1..20, 21..40, 41..60, ...).
> In other words, I need a possibility to get row n..m from the table
> "addresses"
> using the index (type, lastname).
>
> I tried to use the "between" or the "Minus" operator.
> With small numbers of n it worked fine, but when the number grow
> bigger it took
> to much time (because I have to execute this select-statement almost
> every second).
> I think with the "between" and the "Minus" operator, Oracle always
> creates a new cursor with many results although I only need 20 rows.
>
> Of course I need to have the possibility to jump backwards to a former
> block which I already the read before, so it's not just some
> fetch-statements in the cursor.
>
> If you have any idea or suggestions, please let me know.
> Thanks in advance.
> Ralf

First beware that there is no garantee that the rows in any table are returned in the order of entry - there is no fifo function, except if you have a sequence in the ID column and order by ID.

The solution could be like to select n..m rows

select * from

   (select ID, type, firstname, lastname     from adresses
    where type = :b1
    and rownum < :m
    minus
    select ID, type, firstname, lastname     from adresses
    where type = :b1
    and rownum < :n
   );
In some cases the rownum can be substituted by ID.

But you cant avoid selecting up to max or at least min value twice.

If you need the order by ID, you have to nest the selects one inline view deeper.
Or if you need some ordering by ID, build an index on type, id, lastname and force the index use by hint to get the rows in ID order by index lookup.

Regards

/Svend Jensen Received on Mon Mar 10 2003 - 20:32:47 CET