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
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);