HeavenHe() : 该方法已经试过了,对速度没有什么帮助。 造成该SQL运行曼的原因是 (select distinct ZBTDP_MC14.KAISHA_CD,ZBTDP_MC14.HINMEI_CD,ZBTDP_MC14.SBU_CD from ZBTDP_MC14 union select distinct ZBTDP_MC15.KAISHA_CD,ZBTDP_MC15.HINMEI_CD,ZBTDP_MC15.SBU_CD from ZBTDP_MC15) ZBTDP_MC 该句。当union后的结果比较多的时候,再与ZBTDP_DJ64关联,需要做全表遍历,慢就慢在这里了。 多谢!
from ZBTDP_DJ64 a,(select distinct ZBTDP_MC14.KAISHA_CD,ZBTDP_MC14.HINMEI_CD,ZBTDP_MC14.SBU_CD from ZBTDP_MC14 where SBU_CD = '02') b,(select distinct ZBTDP_MC15.KAISHA_CD,ZBTDP_MC15.HINMEI_CD,ZBTDP_MC15.SBU_CD from ZBTDP_MC15 where SBU_CD = '02') c where (a.KAISHA_CD=b.KAISHA_CD and a.HINMEI_CD=b.HINMEI_CD) or (a.KAISHA_CD=c.KAISHA_CD and a.HINMEI_CD=c.HINMEI_CD) and a.KAISHA_CD = '000' and a.TSUCHI_KBN != '4' and a.TSUCHI_KBN != 'C' and a.SHUTSU_YMD = '20030101'
试试下面这个方法。我分析你原来的曼的原因,很大程度上是在union的时候没有任何条件限制。而查询中,条件基本上都是针对zbtdp_dj64,所以...... SELECT COUNT(*) AS counter FROM zbtdp_dj64 T64 WHERE ZBTDP_MC.SBU_CD = '02' AND ZBTDP_DJ64.KAISHA_CD = '000' AND ZBTDP_DJ64.TSUCHI_KBN != '4' AND ZBTDP_DJ64.TSUCHI_KBN != 'C' AND ZBTDP_DJ64.SHUTSU_YMD = '20030101' AND ( EXISTS( SELECT 1 FROM ZBTDP_MC14 T14 WHERE T64.KAISHA = T14.KAISHA_CD AND T64.HINMEI_CD = T14.HINMEI_CD ) OR EXISTS( SELECT 1 FROM ZBTDP_MC15 T15 WHERE T64.KAISHA = T15.KAISHA_CD AND T64.HINMEI_CD = T15.HINMEI_CD ) );
该方法已经试过了,对速度没有什么帮助。
造成该SQL运行曼的原因是
(select distinct ZBTDP_MC14.KAISHA_CD,ZBTDP_MC14.HINMEI_CD,ZBTDP_MC14.SBU_CD
from ZBTDP_MC14
union
select distinct ZBTDP_MC15.KAISHA_CD,ZBTDP_MC15.HINMEI_CD,ZBTDP_MC15.SBU_CD
from ZBTDP_MC15) ZBTDP_MC
该句。当union后的结果比较多的时候,再与ZBTDP_DJ64关联,需要做全表遍历,慢就慢在这里了。
多谢!
and a.TSUCHI_KBN != '4'
and a.TSUCHI_KBN != 'C'
and a.SHUTSU_YMD = '20030101'
快是快了,但出来的结果不是我所要得。
多谢
1. 分析查询计划,看看每一步都做了什么操作,
系统的开销主要花在什么地方
2. 你的SQL 中有distinct关键字,即要做排序操作.
试着调整 SORT_AREA_SIZE的值.
先谢谢了。(该贴肯定是结的,分不够我再加,希望大家能畅所欲言)
非常感谢!!
SELECT COUNT(*) AS counter
FROM zbtdp_dj64 T64
WHERE ZBTDP_MC.SBU_CD = '02'
AND ZBTDP_DJ64.KAISHA_CD = '000'
AND ZBTDP_DJ64.TSUCHI_KBN != '4'
AND ZBTDP_DJ64.TSUCHI_KBN != 'C'
AND ZBTDP_DJ64.SHUTSU_YMD = '20030101'
AND ( EXISTS(
SELECT 1
FROM ZBTDP_MC14 T14
WHERE T64.KAISHA = T14.KAISHA_CD
AND T64.HINMEI_CD = T14.HINMEI_CD
)
OR EXISTS(
SELECT 1
FROM ZBTDP_MC15 T15
WHERE T64.KAISHA = T15.KAISHA_CD
AND T64.HINMEI_CD = T15.HINMEI_CD
)
);