SELECT page-by-page from large dataset?

From: Kenny Yu <kyu_at_biodiscovery.com>
Date: Mon, 8 Apr 2002 17:04:41 -0700
Message-ID: <ub4buvjrbtot88@corp.supernews.com>

I am sure many people have done this but I have to learn it.

I need to select from a large table that has millions of rows. I have to get a 100 (might make this a variable) at a time and enable page-down functionality on the GUI.

I know I need to use rownum to limit the number of record for each select and use rowid as the progress marker. I notice that if I don't do 'order by', the records are ordered by rowid.

However, I suspect it is inefficient to add rowid to the select_list since I only need the value of the last one. Definitely a bad idea to send all the rowid's to the GUI client. I do need to retrieve the PK column, but the rows are not ordered by the pk_col so I can't use the pk_col to mark the progress. Therefore I am thinking of doing the following:

select pk_col, other_cols
  from big_tab
 where rowid >

     ( select rowid from big_tab
       where pk_col = last_pk )

   and rownum <= 100

Flames welcome.

Kenny Received on Mon Apr 08 2002 - 19:04:41 CDT