Oracle ROWNUM

1. Use the analytic function is the best way.
row_number() over ( partition by col1 order by col2 )

for example: get records between 100 and 150, order by tname.

select tname,tabtype from (
select tname,tabtype,row_number() over ( order by tname ) rn from tab
)
where rn between 100 and 150;

2. use rownum
select tname,tabtype from (
select tname,tabtype,rownum rn from tab where rownum <= 150
)
where rn >= 100;

Use rownum, you can not order the whole records when using order by, it will get the front records then order these records.

But if the order by field is primary key, it is a exception, it will order the whole records first, then get the front records.

[@more@]