请问各位大侠下面的两个SQL语句是否可以优化,现在查询速度比较慢,大约需要10秒左右
1、
SELECT GD_WPARTKEY.K_ID, GD_WPARTKEY.K_NAME, GD_WPARTKEY.C_CARTYPE,
GD_WPARTKEY.K_REMARK, GD_WPARTKEY.U_ID, GD_WPARTKEY.F_ID,
GD_WPARTKEY.K_WM, GD_WPARTKEY.K_XH, GF_PFACTORY.F_NAME,
GU_UKPART.U_NAME, GD_WCOLLECTOR.C_NAME, GD_WCOLLECTOR.C_ID
FROM GU_UKPART, GD_WCOLLECTOR,
GF_PFACTORY, GD_CKPART, GD_WPARTKEY
WHERE GU_UKPART.U_ID (+) = GD_WPARTKEY.U_ID AND
GD_WPARTKEY.K_ID = GD_CKPART.K_ID AND
GD_CKPART.C_ID = GD_WCOLLECTOR.C_ID AND
GD_WPARTKEY.F_ID = GF_PFACTORY.F_ID AND
(GD_WCOLLECTOR.C_ID = :CollectorID) AND
(GD_WPARTKEY.C_CARTYPE = :CarType OR
GD_WPARTKEY.C_CARTYPE = '0')
2、
SELECT GD_WPARTKEY.C_CARTYPE, GD_WPARTKEY.F_ID, GD_WPARTKEY.K_ID, GD_WPARTKEY.K_NAME, GD_WPARTKEY.K_REMARK,GD_WPARTKEY.U_ID, GF_PFACTORY.F_NAME, GD_CKPART.C_ID, GU_UKPART.U_NAME
FROM GD_WPARTKEY ,GD_CKPART , GF_PFACTORY,GU_UKPART
WHERE (GD_WPARTKEY.U_ID = GU_UKPART.U_ID)AND( GD_WPARTKEY.F_ID = GF_PFACTORY.F_ID)AND(GD_WPARTKEY.K_ID = GD_CKPART.K_ID) AND( NOT EXISTS(SELECT K_ID FROM GD_WCARINFORKEY WHERE (GD_WCARINFORKEY.K_ID=GD_WPARTKEY.K_ID) AND (C_BOTTOMNO = :BOTNO))) AND ( EXISTS(SELECT C_CARTYPE FROM CS_CARINFO WHERE (CS_CARINFO.C_CARTYPE=GD_WPARTKEY.C_CARTYPE)AND (C_BOTTOMNO = :BOTNO)) OR GD_WPARTKEY.C_CARTYPE = '0')ORDER BY GD_WPARTKEY.K_XH
1、
SELECT GD_WPARTKEY.K_ID, GD_WPARTKEY.K_NAME, GD_WPARTKEY.C_CARTYPE,
GD_WPARTKEY.K_REMARK, GD_WPARTKEY.U_ID, GD_WPARTKEY.F_ID,
GD_WPARTKEY.K_WM, GD_WPARTKEY.K_XH, GF_PFACTORY.F_NAME,
GU_UKPART.U_NAME, GD_WCOLLECTOR.C_NAME, GD_WCOLLECTOR.C_ID
FROM GU_UKPART, GD_WCOLLECTOR,
GF_PFACTORY, GD_CKPART, GD_WPARTKEY
WHERE GU_UKPART.U_ID (+) = GD_WPARTKEY.U_ID AND
GD_WPARTKEY.K_ID = GD_CKPART.K_ID AND
GD_CKPART.C_ID = GD_WCOLLECTOR.C_ID AND
GD_WPARTKEY.F_ID = GF_PFACTORY.F_ID AND
(GD_WCOLLECTOR.C_ID = :CollectorID) AND
(GD_WPARTKEY.C_CARTYPE = :CarType OR
GD_WPARTKEY.C_CARTYPE = '0')
2、
SELECT GD_WPARTKEY.C_CARTYPE, GD_WPARTKEY.F_ID, GD_WPARTKEY.K_ID, GD_WPARTKEY.K_NAME, GD_WPARTKEY.K_REMARK,GD_WPARTKEY.U_ID, GF_PFACTORY.F_NAME, GD_CKPART.C_ID, GU_UKPART.U_NAME
FROM GD_WPARTKEY ,GD_CKPART , GF_PFACTORY,GU_UKPART
WHERE (GD_WPARTKEY.U_ID = GU_UKPART.U_ID)AND( GD_WPARTKEY.F_ID = GF_PFACTORY.F_ID)AND(GD_WPARTKEY.K_ID = GD_CKPART.K_ID) AND( NOT EXISTS(SELECT K_ID FROM GD_WCARINFORKEY WHERE (GD_WCARINFORKEY.K_ID=GD_WPARTKEY.K_ID) AND (C_BOTTOMNO = :BOTNO))) AND ( EXISTS(SELECT C_CARTYPE FROM CS_CARINFO WHERE (CS_CARINFO.C_CARTYPE=GD_WPARTKEY.C_CARTYPE)AND (C_BOTTOMNO = :BOTNO)) OR GD_WPARTKEY.C_CARTYPE = '0')ORDER BY GD_WPARTKEY.K_XH
还有括号不要用得那么多,太多了容易混乱。and优先级比or高,必要的地方再用
SELECT a.C_CARTYPE, a.F_ID, a.K_ID, a.K_NAME, a.K_REMARK,a.U_ID, c.F_NAME, b.C_ID, d.U_NAME
FROM GD_WPARTKEY a ,GD_CKPART b, GF_PFACTORY c,GU_UKPART d
WHERE GD_WPARTKEY.U_ID = GU_UKPART.U_ID AND
a.F_ID = c.F_ID AND
a.K_ID = b.K_ID AND
NOT EXISTS(
SELECT K_ID FROM GD_WCARINFORKEY
WHERE K_ID=a.K_ID AND
C_BOTTOMNO = :BOTNO) AND (
EXISTS(SELECT C_CARTYPE FROM CS_CARINFO
WHERE C_CARTYPE=a.C_CARTYPE AND
C_BOTTOMNO = :BOTNO)
OR a.C_CARTYPE = '0')
ORDER BY a.K_XH
, GD_WPARTKEY.K_NAME
, GD_WPARTKEY.C_CARTYPE
, GD_WPARTKEY.K_REMARK
, GD_WPARTKEY.U_ID
, GD_WPARTKEY.F_ID
, GD_WPARTKEY.K_WM
, GD_WPARTKEY.K_XH
, GF_PFACTORY.F_NAME
, GU_UKPART.U_NAME
, GD_WCOLLECTOR.C_NAME
, GD_WCOLLECTOR.C_ID
FROM GU_UKPART
, GD_WCOLLECTOR
, GF_PFACTORY
, GD_CKPART
, GD_WPARTKEY
WHERE GU_UKPART.U_ID(+) = GD_WPARTKEY.U_ID
AND GD_WPARTKEY.K_ID = GD_CKPART.K_ID
AND GD_CKPART.C_ID = GD_WCOLLECTOR.C_ID
AND GD_WPARTKEY.F_ID = GF_PFACTORY.F_ID
AND GD_WPARTKEY.C_CARTYPE IN(:CarType,'0')
AND GD_WCOLLECTOR.C_ID = :CollectorID;
先确认用上了CBO