SELECT *
FROM (
SELECT A.*,
FROM (select *, RowNum AS RN
from Contract
where EnterpriseID ='123456'
and not exists (Select 1
from OtherContract vq
where EnterpriseID ='123456'
and isfinish='1'
and ContractId = OtherContractId)) A
WHERE RN<=50) B
WHERE RN>=10
这样的一个分页语句执行时间超长,怎样才能提高它的运行效率?把A对应的查询语句换成一般的查询语句速度就很快,难道是not exists的原因?但单独执行A对应的sql速度也是很快的,就是到分页的时候就不行了,help~~~~还有个问题是,改成下面的语句查询,速度是很快,但其实记录号大于1就查询不到数据了,这又是为什么?
select * from (
select *
from Contract
where EnterpriseID ='123456'
and not exists (Select 1
from OtherContract vq
where EnterpriseID ='123456'
and isfinish='1'
and ContractId = OtherContractId))
where RowNum>=2 and RowNum<=10
FROM (
SELECT A.*,
FROM (select *, RowNum AS RN
from Contract
where EnterpriseID ='123456'
and not exists (Select 1
from OtherContract vq
where EnterpriseID ='123456'
and isfinish='1'
and ContractId = OtherContractId)) A
WHERE RN<=50) B
WHERE RN>=10
这样的一个分页语句执行时间超长,怎样才能提高它的运行效率?把A对应的查询语句换成一般的查询语句速度就很快,难道是not exists的原因?但单独执行A对应的sql速度也是很快的,就是到分页的时候就不行了,help~~~~还有个问题是,改成下面的语句查询,速度是很快,但其实记录号大于1就查询不到数据了,这又是为什么?
select * from (
select *
from Contract
where EnterpriseID ='123456'
and not exists (Select 1
from OtherContract vq
where EnterpriseID ='123456'
and isfinish='1'
and ContractId = OtherContractId))
where RowNum>=2 and RowNum<=10
解决方案 »
- oracle9i同一实例下但不同用户下相同结构的表(多张)之间进行数据复制
- 安装Oracle10g时出现“createfile error 32 when trying set file time”
- 请教下oracle SQLplus 8.0 如何调出修改过的原始数据和数据语言。
- 学习ORACLE
- oracle中存取大字段问题:long raw字段存储文件
- 数据库的连接问题,出现错误ORA-12535: TNS: 操作超时
- 关于ora-01033求救
- 我在xp下安装Oracle 9i为什么只好使了2次就不好用了呢!?
- nvl2
- Oracle数据库语法错误,但小弟没查出来
- 谁能帮忙改一个SQL语句!!!!!!
- 救命啊,怎么还是找不到 OracleClient 域??
SELECT A.*,
FROM (select *, RowNum AS RN
from Contract
where EnterpriseID ='123456'
and not exists (Select 1
from OtherContract vq
where EnterpriseID ='123456'
and isfinish='1'
and ContractId = OtherContractId)) A
where rn>=10 and rn<=50不知這速度怎樣
你的写法和我第一种写法其实是一样的,速度依旧慢 to:skystar99047(天星)
有没有什么办法?
select * from (
select *
from Contract
where EnterpriseID ='123456'
and not exists (Select 1
from OtherContract vq
where EnterpriseID ='123456'
and isfinish='1'
and ContractId = OtherContractId))
where rownum<=10
minus
select * from (
select *
from Contract
where EnterpriseID ='123456'
and not exists (Select 1
from OtherContract vq
where EnterpriseID ='123456'
and isfinish='1'
and ContractId = OtherContractId))
where rownum<=2
1.加大sort_area_SIZE
2.内层查询返回ROWID,基本结构如下:
select * from tab_name
where rowid in (
select rid from (
select rowid as rid,rownum as rn from tab_name
where ...
and rownum<1000)
where rn>900);