ROWNUM for Page selection

Giganews Newsgroups
Subject: ROWNUM for Page selection
Posted by:  Daniel Paetzold (daniel.paetzo…
Date: Fri, 07 Jan 2005

Hello and happy new year NG ;),

is there any better solution for the following query

select a from (select a, ROWNUM "ROW_SUB" from t where ROWNUM<=200)
where "ROW_SUB">100;

to select only rows 101 to 200 from the tabledata?

"better" for me would be:
A. faster processing for Oracle or/and
B. avoiding the sub-query

Does anybody knows any way to do this with all ansi-sql-compatible
databases (i don't think that rownum is part of ansi-sql)?

The query will be executed quite often and i'd like to speed it up as
much as possible (without thinking about the structure of the data for now).