做项目忙了一天,头昏脑胀,求助下大家帮忙看下两段SQLOracle 10G 版本第一段select ebpoE.zbthnr as zbthnr1,ebpoE.Zqgdh,
o.zbthnr2,
ebpoE.ebeln,
ebpoE.ebelp from t_sap_eban ebpoE
left join (
select eban.banfn,eban.bnfpo,decode(nvl(ekpo.ebeln_pre, ''),
'',
(select eb.zbthnr
from t_sap_eban eb
where eb.ebeln || '-' || eb.ebelp =
ekpo.konnr || '-' || ekpo.ktpnr
and
eb.loekz is null and rownum =1),
(select eb.zbthnr
from t_sap_eban eb
where eb.ebeln || '-' || eb.ebelp =
ekpo.ebeln_pre || '-' || ekpo.ebelp_pre
and
eb.loekz is null and rownum =1)) as zbthnr2
from t_sap_eban eban, t_sap_ekpo ekpo
WHERE nvl(eban.ebeln,'') || '-' ||nvl(eban.ebelp,'') =
ekpo.ebeln || '-' || ekpo.ebelp
) o
on o.banfn||'-'||o.bnfpo = ebpoE.banfn||'-'||ebpoE.bnfpo
where ebpoE.Ebeln = '4700038450'
第二段select ebpoE.zbthnr as zbthnr1,ebpoE.Zqgdh,
(select decode(nvl(ekpo.ebeln_pre, ''),
'',
(select nvl(eb.zbthnr, '')
from t_sap_eban eb
where eb.ebeln || '-' || eb.ebelp =
ekpo.konnr || '-' || ekpo.ktpnr
and
eb.loekz is null and rownum =1),
(select nvl(eb.zbthnr, '')
from t_sap_eban eb
where eb.ebeln || '-' || eb.ebelp =
ekpo.ebeln_pre || '-' || ekpo.ebelp_pre
and
eb.loekz is null and rownum =1)) as zbthnr2
from t_sap_eban eban, t_sap_ekpo ekpo
WHERE eban.ebeln || '-' || eban.ebelp =
ekpo.ebeln || '-' || ekpo.ebelp
and eban.banfn|| '-' ||eban.bnfpo = ebpoE.banfn|| '-' ||ebpoE.bnfpo)
as zbthnr2,
ebpoE.ebeln,
ebpoE.ebelp
from t_sap_eban ebpoE where nvl(ebpoE.Ebeln,'') = '4700038450'
这两段SQL其实都是一个意思就是字段zbthnr2通过判断ekpo表相关的字段是否有值,如果没有值则执行一段逻辑判断获取一个值来作为该值第一段的SQL是采用先将值取出然后通过条件左链接获得该值第二段的SQL是采用子查询的方式获得zbthnr2该值按照经验来说的话应该是第一段的SQL的执行时间会短一些。。但是现在是这种情况,第一段SQL如果不加" where ebpoE.Ebeln = '4700038450' ",查询所有值为10秒
但是加了" where ebpoE.Ebeln = '4700038450' " 的话至少10分钟都出不来数据。而第二段的SQL中 如果加了" where ebpoE.Ebeln = '4700038450' " 则查询对应值为20秒左右
如果不加则至少10分钟都出不来数据。EBAN 中的主键为eban.banfn,eban.bnfpo 并且建立了eban.ebeln 与eban.ebelp相关索引EKPO 中的主键为eban.ebeln 与eban.ebelp
请大虾们帮帮忙了
o.zbthnr2,
ebpoE.ebeln,
ebpoE.ebelp from t_sap_eban ebpoE
left join (
select eban.banfn,eban.bnfpo,decode(nvl(ekpo.ebeln_pre, ''),
'',
(select eb.zbthnr
from t_sap_eban eb
where eb.ebeln || '-' || eb.ebelp =
ekpo.konnr || '-' || ekpo.ktpnr
and
eb.loekz is null and rownum =1),
(select eb.zbthnr
from t_sap_eban eb
where eb.ebeln || '-' || eb.ebelp =
ekpo.ebeln_pre || '-' || ekpo.ebelp_pre
and
eb.loekz is null and rownum =1)) as zbthnr2
from t_sap_eban eban, t_sap_ekpo ekpo
WHERE nvl(eban.ebeln,'') || '-' ||nvl(eban.ebelp,'') =
ekpo.ebeln || '-' || ekpo.ebelp
) o
on o.banfn||'-'||o.bnfpo = ebpoE.banfn||'-'||ebpoE.bnfpo
where ebpoE.Ebeln = '4700038450'
第二段select ebpoE.zbthnr as zbthnr1,ebpoE.Zqgdh,
(select decode(nvl(ekpo.ebeln_pre, ''),
'',
(select nvl(eb.zbthnr, '')
from t_sap_eban eb
where eb.ebeln || '-' || eb.ebelp =
ekpo.konnr || '-' || ekpo.ktpnr
and
eb.loekz is null and rownum =1),
(select nvl(eb.zbthnr, '')
from t_sap_eban eb
where eb.ebeln || '-' || eb.ebelp =
ekpo.ebeln_pre || '-' || ekpo.ebelp_pre
and
eb.loekz is null and rownum =1)) as zbthnr2
from t_sap_eban eban, t_sap_ekpo ekpo
WHERE eban.ebeln || '-' || eban.ebelp =
ekpo.ebeln || '-' || ekpo.ebelp
and eban.banfn|| '-' ||eban.bnfpo = ebpoE.banfn|| '-' ||ebpoE.bnfpo)
as zbthnr2,
ebpoE.ebeln,
ebpoE.ebelp
from t_sap_eban ebpoE where nvl(ebpoE.Ebeln,'') = '4700038450'
这两段SQL其实都是一个意思就是字段zbthnr2通过判断ekpo表相关的字段是否有值,如果没有值则执行一段逻辑判断获取一个值来作为该值第一段的SQL是采用先将值取出然后通过条件左链接获得该值第二段的SQL是采用子查询的方式获得zbthnr2该值按照经验来说的话应该是第一段的SQL的执行时间会短一些。。但是现在是这种情况,第一段SQL如果不加" where ebpoE.Ebeln = '4700038450' ",查询所有值为10秒
但是加了" where ebpoE.Ebeln = '4700038450' " 的话至少10分钟都出不来数据。而第二段的SQL中 如果加了" where ebpoE.Ebeln = '4700038450' " 则查询对应值为20秒左右
如果不加则至少10分钟都出不来数据。EBAN 中的主键为eban.banfn,eban.bnfpo 并且建立了eban.ebeln 与eban.ebelp相关索引EKPO 中的主键为eban.ebeln 与eban.ebelp
请大虾们帮帮忙了
也有可能解释计划本身就不对
重建相关索引试试?
eban.banfn|| '-' ||eban.bnfpo = ebpoE.banfn|| '-' ||ebpoE.bnfpo来获取联结主键的匹配值的时候,不是访问索引。。所以全部改成 eban.banfn = eban.bnfpo and ebpoE.banfn = ebpoE.bnfpo 就快起来了
但是现在问题又来了 使用count(*)来统计数据的时候好像很慢。。怀疑是不是数据量太大