select * from table1 where rownum <= 85 minus select * from table1 where rownum <= 10;
select * from (select *,rownum rm from table1) where rm between 10 and 85;
select * from table1 where rownum <= 85 minus select * from table1 where rownum <= 10; 结论 执行时间:0.016 secondsselect * from (select *,rownum rm from table1) where rm between 10 and 85; 执行时间:0.031 seconds假设表只有6条记录如果有100万条记录的话时间差别是很大的!
select * from emp where rowid between (select max(rowid) from emp where rownum <= 10) and (select max(rowid) from emp where rownum <= 85);
minus 根你的 sort_area_size 设置有关吧 你的记录少 是在内存排序的
select * from (select a.*,row_number() over(order by rownum) rm from table_name a) where rm between 10 and 85
select * from table1 where rownum <= 85 minus select * from table1 where rownum <= 10;
select * from table1 where rownum between 10 and 85; 为什么不行哪?
对于where rownum ...的操作只支持<,<=,=1这样的操作,例如:where rownum < 100; where rownum <= 100; where rownum = 1; 以上3句是正确的where rownum = 2;此语句是错误语句,请注意,使用rownum等于号时只能用=1!!!
补充:伪列ROWNUM从1开始递增,但以下语句依然可以顺利执行,不过返回的是所有记录: select * from emp where rownum > 0
你可以这样实现: select t1.r_id,t1.id,t1.name from ( select rownum as r_id,t.id,t.name from tbluser t ) t1 where t1.r_id <= 10 and t1.r_id >= 4
minus
select * from table1 where rownum <= 10;
minus
select * from table1 where rownum <= 10;
minus
select * from table1 where rownum <= 10;
结论
执行时间:0.016 secondsselect * from (select *,rownum rm from table1) where rm between 10 and 85;
执行时间:0.031 seconds假设表只有6条记录如果有100万条记录的话时间差别是很大的!
minus
select * from table1 where rownum <= 10;
where rownum <= 100;
where rownum = 1;
以上3句是正确的where rownum = 2;此语句是错误语句,请注意,使用rownum等于号时只能用=1!!!
select * from emp where rownum > 0
select t1.r_id,t1.id,t1.name
from
(
select rownum as r_id,t.id,t.name
from tbluser t
) t1
where t1.r_id <= 10 and t1.r_id >= 4