这条语句 内部查询很快,查询时间为0.8秒,查询结果为258 条,
如果将内部查询结果再做一次 CODE like '43%' 过滤, 速度也很快,
但是,我将2条语句写在一起,查询速度非常慢,请教这是什么原因,应该如何优化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.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
如果将内部查询结果再做一次 CODE like '43%' 过滤, 速度也很快,
但是,我将2条语句写在一起,查询速度非常慢,请教这是什么原因,应该如何优化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.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备份的数据库如何自动拷贝到另一台服务器上?
- SQL语句
- oracle触发器问题
- [运行错误]ORA-01481: invalid number format model
- 初学者提问:建好oracle数据库后如何建立用户,表?及在哪里建呀?
- timeout while wait to lock object pakage1,什么问题
- 我的全连接报告错误,大家帮我看一下(立即结帖)
- 设为手动启动后出现的问题,急需解决,谢
- 请问各位dx:提示“maximum of 20 enabled roles exceeded“,该如何解决?谢谢。(无内容)
- 高分求救!!oracle的安装问题(在线等)
- 有点难度的两表查询语句
- oracle spatial空间函数出错
大家都不要猜测,原因就在 like '43%' 我不加这个条件查询快,加了条件查询慢,其它的都是次要的
explain plan for select .....;SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));