SORT ORDER BY STOPKEY

select  *  from ( select rownum as rn ,a.* from ( select  
*   from t100 a  
order by object_id desc )a where rownum<=40 ) a  where rn>=1


SQL> 
SQL> select count(*) from t100;

  COUNT(*)
----------
   3020896
   
 BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SYSTEM',
                                tabname          => 'T100',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size repeat',
                                no_invalidate    => FALSE,
                                degree           => 8,
                                cascade          => TRUE);
END;

---高级执行计划:
11G：
alter session set statistics_level=all; ---再运行SQL

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));


select *
  from (select rownum as r