以下语句 分2层,内层查询速度很快,0.7秒就能查询出结果,结果不多,只有200多条,
因此如果 oracle 按照 先执行内部查询,再执行外部查询 的先后顺序,结果 是很快的,但是这个整体查询 速度却非常的慢,我觉得oracle 并未先查询内部,请求,这个如何优化。select * from(
select f.code codes , a.id,a.sealname,a.imagetype,a.proposer,a.orgid,a.workphone,a.mobile,a.email,
a.sealtype,a.homephone,a.address,a.cardtype,a.card,a.state,a.country,a.province,
a.location,a.business,a.tax,a.sealorg,a.certificateorg, b.name as proposername,
c.name as orgname,d.name as sealtypename , e.state as statename, f.name as sealorgname,
a.enc,a.hashcode,a.code,a.endday FROM tbl_sealrequest a,tbl_user b,tbl_organization c,tbl_sealtype d,
tbl_sealrequeststate e,tbl_organization f WHERE a.id IS NOT NULL and a.state <> 0 and a.proposer = b.id
and a.orgid = c.id and a.sealtype = d.id and a.state = e.id and a.sealorg = f.id AND
a.state = 1
) z where z.codes like '43%' ORDER BY z.id desc
因此如果 oracle 按照 先执行内部查询,再执行外部查询 的先后顺序,结果 是很快的,但是这个整体查询 速度却非常的慢,我觉得oracle 并未先查询内部,请求,这个如何优化。select * from(
select f.code codes , a.id,a.sealname,a.imagetype,a.proposer,a.orgid,a.workphone,a.mobile,a.email,
a.sealtype,a.homephone,a.address,a.cardtype,a.card,a.state,a.country,a.province,
a.location,a.business,a.tax,a.sealorg,a.certificateorg, b.name as proposername,
c.name as orgname,d.name as sealtypename , e.state as statename, f.name as sealorgname,
a.enc,a.hashcode,a.code,a.endday FROM tbl_sealrequest a,tbl_user b,tbl_organization c,tbl_sealtype d,
tbl_sealrequeststate e,tbl_organization f WHERE a.id IS NOT NULL and a.state <> 0 and a.proposer = b.id
and a.orgid = c.id and a.sealtype = d.id and a.state = e.id and a.sealorg = f.id AND
a.state = 1
) z where z.codes like '43%' ORDER BY z.id desc
解决方案 »
- sql合并数据表,有多条变成一条
- 导入DMP时,出错如下错误,但我选择了50行数据,看到导入了
- sql语句问题~~
- Oracle11中如何升级APEX到3.2
- 紧急求Oracle sql语句的写法,sqlserver行,oracle不行!在线等!
- 急求各位高手帮忙解决
- Oracle 10g express能否生成带用insert语句插入全部数据的SQL脚本? (generate DDL)
- 大家好!本人刚开始学oracle感觉里面所提到的数据类型很多,如sql,pl/sql,oracle数据类型,可以说一下它们之间的关系吗?谢谢!
- 求助ORACLE存储过程和触发器的问题,急急!!!!!!
- 排序的问题
- fckeditor编辑器问题,不怕分少,只怕解决不了!
- 有点难度的两表查询语句
a.sealtype,a.homephone,a.address,a.cardtype,a.card,a.state,a.country,a.province,
a.location,a.business,a.tax,a.sealorg,a.certificateorg, b.name as proposername,
c.name as orgname,d.name as sealtypename , e.state as statename, f.name as sealorgname,
a.enc,a.hashcode,a.code,a.endday
FROM tbl_sealrequest a,tbl_user b,tbl_organization c,tbl_sealtype d,
tbl_sealrequeststate e,tbl_organization f
WHERE a.proposer = b.id
and a.orgid = c.id
and a.sealtype = d.id
and a.state = e.id
and a.sealorg = f.id
and f.code like '43%'
AND a.id IS NOT NULL
and a.state = 1
ORDER BY a.id desc;