分页语句:
select *
from (select t.*, rownum rn
from (select * from test where object_id <= 1000) t)
where rn >= 1
and rn <= 10;
---谓词推不进去,因为有rownum会自动生成一个view
1.
select *
from (select *
from (select a.*, rownum rn
from (select * from test where object_id <= 1000) a)
where rownum <= 10)
where rn >= 0;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
分页框架
select * from
(
select * from
(
select a.*,rownum rn
from
(
---这里面放 SQL 外面是框架都不用改
) a
) where rownum<=10
) where rn>=0;
写一个脚本全自动化的抓出错误的分页
提醒错误的分页语句是不是就只有一个rownum。
select *
from (select *
from (select a.*, rownum rn
from (select * from test where object_id <= 1000) a)
where rownum <= 10)
where rn >= 0;
1. 符合sql分页框架
2.分页必须走索引
3.分页语句中只要发现 SORT ORDER BY 百分之80,90是错的,因为rows多了就会排序时间过长。
from (select *
from (select a.*, rownum rn
from (select /*+ index(test index_obj) */ * from test where object_id <= 100000 order by owner) a)
where rownum <= 10)
where rn >= 0;
分页语句不能有group by
select ... from a, (.... group by ...) b
----------------------------------------------------------------------页面展示