Re: Best way to retrieve rows

Thanks Jerry. That's what I had in mind, but if a table is big, has many rows, retrieving the tail portion of it could be very slow (?) compared to getting the head portion. For a 10,000,000-row table, it may take just a split of a second to get rows 1-100, but how about rows 9,999,901-10,000,000?

A simple comparison showed that for a 1,100,000-row table, the retrival of rows 51 to 100 (50 rows) took 0.23 seconds; while the retrieval of rows 1,000,001 to 1,000,050 (also 50 rows) took more than 4 minutes.

That's why I am asking for a better solution - other than the combination of ROWNUM and MINUS - to "page through" any arbitrary portion of a table (at comparable access speed).

Robert Xu

Jerry Gitomer (jgitomer_at_hbsrx.com) wrote:
: Mea culpa, mea culpa

: Sorry folks I didn't read all of the way through before I slammed out a
: response to the wrong problem. Now I feel obligated to come up with a valid
: answer. So,

: Lets modify the SELECT MINUS SELECT to use parameters instead of fixed
: values for the rownum and then either call it from or embed it in a PL/SQL
: loop. Given a page size of N we could then take the value of the lowest
: rownum in the current page and either add/subtract N in order to retrieve
: the next/prior page. For test purposes a SQL*Plus version could be:

: SELECT * FROM <table> WHERE rownum < (&1 + 10)
: MINUS
: SELECT * FROM <table> WHERE rownum < &1;

: If this script is stored as x.sql

: @x 1 will display the first 10 rows

: @x 5 will display the rows 5 through 14

: @x 10 will display the rows 10 through 19

: regards

: Jerry Gitomer
: ----------------------------------------------
Received on Fri Jun 04 1999 - 17:55:19 CDT