Oracle分页SQL

Oracle 分页
select a.*, rownum r
  from (select * from lg_order o order by o.order_id) a
 where rownum <= 20
   and rownum >= 10

select *
  from (select a.*, rownum r
          from (select * from lg_order o order by o.order_id) a
         where rownum <= 20) b
 where r >= 10

select *
  from (select rid
          from (select rownum rn, rid
                  from (select rowid rid from lg_order o order by o.order_id)
                 where rownum <= 20)
         where rn >= 10) t1,
       lg_order t2
 where t2.rowid = t1.rid

第一句是错误的 由于rownum是一个总是从1开始的伪列，Oracle 认为这种条件不成立，查不到记录
执行顺序从内到外，从右到左
直接取物理地址最快

 

转自http://www.blogjava.net/EvanLiu/archive/2008/03/23/188042.html