select bc.corpid, bc.memberid, bc.feenumber, bc.shortnumber, bc.groupflag,
ci.validflag,
cf.feetype
from corpmember bc, corpinfo ci, corpfeetype cf
where bc.shortNumber=:1
and bc.corpid=ci.corpid
and ci.corpid=cf.corpid
and ci.corpid in (select c1.corpid from corpinfo c1, corpinfo c2 where c1.PARENTCORPID = c2.PARENTCORPID and c2.PARENTCORPID<>0 and c2.corpid=:2 )各位有不有其它的优化办法?由于括号里面的select子句可能会返回多行结果,我觉得使用with..as没什么意义了.如果是使用corelated subquiries是否有性能提升呢?
ci.validflag,
cf.feetype
from corpmember bc, corpinfo ci, corpfeetype cf
where bc.shortNumber=:1
and bc.corpid=ci.corpid
and ci.corpid=cf.corpid
and ci.corpid in (select c1.corpid from corpinfo c1, corpinfo c2 where c1.PARENTCORPID = c2.PARENTCORPID and c2.PARENTCORPID<>0 and c2.corpid=:2 )各位有不有其它的优化办法?由于括号里面的select子句可能会返回多行结果,我觉得使用with..as没什么意义了.如果是使用corelated subquiries是否有性能提升呢?
解决方案 »
- c#使用System.Data.OracleClient 类,插入长度超过32K的clob怎么处理?
- SQL语句中的(+)是啥意思?
- SQL-Loader:装入数据时,怎么把主表生成的记录ID同时写入附表? 详见内容
- 请教关于ORACLE问题
- Oracle 中的没有top 吗?
- Oracle单表的最大存储条数可以达到多少?
- Oracle 新手,求助各位前辈!
- 使用"Provider=OraOLEDB.Oracle;Data Source=R2D2;User ID=foo; Password=bar"连接为何失败?需要安装什么呢?在线等待。
- 记录是按怎样的顺序select出来的?
- 如何让where中不存在的值显示为NULL
- java链接Oracle问题求解
- 如何对一个字符串尾数进行range分区?
FROM CORPMEMBER BC, CORPINFO CI, CORPFEETYPE CF
WHERE BC.SHORTNUMBER = :1
AND BC.CORPID = CI.CORPID
AND CI.CORPID = CF.CORPID
AND CI.PARENTCORPID IS NOT NULL
AND CI.PARENTCORPID = (SELECT PARENTCORPID
FROM CORPINFO
WHERE PARENTCORPID <> 0
AND CORPID = :2
AND ROWNUM <= 1)
(select c1.corpid from corpinfo c1, corpinfo c2 where c1.PARENTCORPID = c2.PARENTCORPID and c2.PARENTCORPID<>0 and c2.corpid=:2 )
上面这个子句可能是返回多行记录结果的.
select bc.corpid, bc.memberid, bc.feenumber, bc.shortnumber, bc.groupflag,
ci.validflag,
cf.feetype
from corpmember bc, corpinfo ci, corpfeetype cf
where bc.shortNumber=:1
and bc.corpid=ci.corpid
and ci.corpid=cf.corpid
and ci.corpid is not null
and ci.corpid in (select /*+ no_merge */ c1.corpid from corpinfo c1, corpinfo c2 where c1.PARENTCORPID = c2.PARENTCORPID and c2.PARENTCORPID<>0 and c2.corpid=:2 )