select /*+ first_row */*
from TABLENAME
where rowid in (select rid from (select rownum rno, rid
from (select rowid rid from TABLENAME
order by FIELD1 desc)
where rownum <= 300020 )
where rno >= 300010);
前提:表TABLENAME按FIELD1建索引。
from TABLENAME
where rowid in (select rid from (select rownum rno, rid
from (select rowid rid from TABLENAME
order by FIELD1 desc)
where rownum <= 300020 )
where rno >= 300010);
前提:表TABLENAME按FIELD1建索引。
select rowid rid from TABLENAME order by FIELD1 desc 此句为什么FIELD1要desc?asc不行吗?
由于有索引,索引中有rowid,所以排序时只从索引中取出需要的rowid,再根据rowid读取记录的内容,这样最快。百万行的,只需要0.几秒就有结果。
此外,排序字段必须是非空的,否则用不上索引。
select /*+ first_row */*
from TABLENAME
where rowid in (select rid from (select rowid rid from TABLENAME
order by FIELD1 desc)
where rownum <= 300020 and rownum>=300010);