现在写的分页语句如下 查询 第1页 1-15 个记录 select * from ( select A.*,rownum r from (select * from table_data where active='Y' order by s_time desc) A where rownum <= 15 ) where r >0 感觉效率很低下,帮我修改一下 提高效率 谢谢
-- 为什么外层还要嵌套呢?-- 这样不就得了? select A.*, rownum r from (select * from table_data where active='Y' order by s_time desc) A where rownum <= 15;
-- 在 s_time、active 字段加个索引CREATE INDEX on table_date(active,s_time);
CREATE INDEX tb_ix1 on table_date(active,s_time);
--嵌套太多了 select a.* ,rownum rn from table_data a where active='Y' and rownum <= 15 order by s_time desc --上面就可以了 就是1到15的 默认是1开始的select * from (select a.* ,rownum rn from table_data a where active='Y' and rownum <= 15 order by s_time desc) t where rn>=n and rn<=m --随你哪条到哪条
这个应该是常用的正确的分页写法了,不过你可以试试分析函数 SELECT * FROM (SELECT t.*, row_number(ORDER BY s_time DESC) r FROM table_data t WHERE active = 'Y') A WHERE r BETWEEN 1 AND 15;
实际在oracle中执行了一下,效率都差不多
select a.*, row_number() over (order by id) rn from a a where rn <= 15; 没有测试过
select A.*, rownum r
from (select * from table_data where active='Y' order by s_time desc) A
where rownum <= 15;
CREATE INDEX tb_ix1 on table_date(active,s_time);
--嵌套太多了
select a.* ,rownum rn
from table_data a
where active='Y' and rownum <= 15
order by s_time desc
--上面就可以了 就是1到15的 默认是1开始的select * from
(select a.* ,rownum rn
from table_data a
where active='Y' and rownum <= 15
order by s_time desc) t
where rn>=n and rn<=m
--随你哪条到哪条
这个应该是常用的正确的分页写法了,不过你可以试试分析函数
SELECT *
FROM (SELECT t.*, row_number(ORDER BY s_time DESC) r FROM table_data t WHERE active = 'Y') A
WHERE r BETWEEN 1 AND 15;
没有测试过