--6行被选择耗时0.032秒
select * from (
select t1.*,rownum rn from (
select * from emp order by sal desc
) t1 where rownum<=10
) where rn>=5;--6行被选择耗时0.062秒
select * from (
select t1.*,rownum rn from (
select * from emp order by sal desc
) t1
) where rn between 5 and 10;--6行被选择耗时0.015秒
select * from emp where rowid in (
select rid from (
select rownum rn,rid from(
select rowid rid from emp order by sal desc
)where rownum<=10
)where rn >=5
) order by sal desc;--6行被选择耗时0.031秒
select * from emp where rowid in (
select rid from (
select rownum rn,rid from(
select rowid rid from emp order by sal desc
)
)where rn between 5 and 10
) order by sal desc;--6行被选择耗时0.031秒
select * from (
select t.*,row_number() over(order by sal desc) rn from emp t
)where rn between 5 and 10;
select * from (
select t1.*,rownum rn from (
select * from emp order by sal desc
) t1 where rownum<=10
) where rn>=5;--6行被选择耗时0.062秒
select * from (
select t1.*,rownum rn from (
select * from emp order by sal desc
) t1
) where rn between 5 and 10;--6行被选择耗时0.015秒
select * from emp where rowid in (
select rid from (
select rownum rn,rid from(
select rowid rid from emp order by sal desc
)where rownum<=10
)where rn >=5
) order by sal desc;--6行被选择耗时0.031秒
select * from emp where rowid in (
select rid from (
select rownum rn,rid from(
select rowid rid from emp order by sal desc
)
)where rn between 5 and 10
) order by sal desc;--6行被选择耗时0.031秒
select * from (
select t.*,row_number() over(order by sal desc) rn from emp t
)where rn between 5 and 10;
解决方案 »
- 【求助】Oracle中触发器如何做exists判断
- 用toad的时候不小心改了一两条记录的数据,如何查找并还原回来?
- 求教,今天用Oracle10g的oem进行实例启动而引起的诸多问题。
- 911 如果向oracle varchar2字段中插入&#的组合特殊字符
- 菜鸟请教:如何在PL/SQL DEVELOPER中创建PACKAGE
- 关于字符编码的问题。
- 分区的问题
- 从oracle8中读数据写入sql2000中,到一数据行出错停止,跟踪发现是varchar2中有一数据"台钻#fe",sql无法识别如何处理这样的问题.
- oracle8i或9i在企业管理器中导入导出数据,每次操作在作业中看都是失败!
- 根据取得的值选择更新字段
- proc中查询oracle库提示ORA-01036: illegal name/number
- 求一动态sql语句
select * from (
select t1.*,rownum rn from (
select * from emp order by sal desc
) t1 where rownum<=10
) where rn>=5;--6行被选择耗时0.062秒
select * from (
select t1.*,rownum rn from (
select * from emp order by sal desc
) t1
) where rn between 5 and 10;
如果是大数据量的话 第一种的优势很大