分页SQL模板

create table page as select * from dba_objects;




BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                tabname          => 'PAGE',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size skewonly',
                                no_invalidate    => FALSE,
                                degree           => 4,
                                cascade          => TRUE);
END;


第1页:
SELECT *FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT * FROM page where object_id >1000 and owner='SYS'
 order by object_id desc) A  
WHERE ROWNUM <= 20  
)  
WHERE RN >= 0

第2页:

SELECT *FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT * FROM page where object_id >1000 and owner='SYS'
 order by object_id desc) A  
WHERE ROWNUM <= 40  
)  
WHERE RN >