本帖最后由 strongdhq 于 2014-9-15 15:49 编辑
SELECT ZY_BQYZ.YSGH,
ZY_BQYZ.YZMC,
ZY_BQYZ.MZCS,
ZY_BQYZ.KSSJ,
ZY_BQYZ.TZSJ,
ZY_BQYZ.YPDJ,
1 as BZ,
'' as YPYF_NAME,
'' as SYPC_NAME,
'' as BZXX,
ZY_BQYZ.MRCS,
'' as YCSL,
ZY_BQYZ.YCJL,
ZY_BQYZ.XMLX,
ZY_BQYZ.YPLX,
ZY_BQYZ.SYBZ,
ZY_BQYZ.ZFPB,
ZY_BQYZ.CZGH,
ZY_BQYZ.SRKS,
ZY_BQYZ.QRSJ,
ZY_BQYZ.YPXH,
ZY_BQYZ.ZYH,
ZY_BQYZ.YZZH,
ZY_BQYZ.JLXH,
ZY_BQYZ.YJZX,
ZY_BQYZ.ZXKS,
ZY_BQYZ.FHGH,
ZY_BQYZ.YPYF,
ZY_BRRY.BRCH,
ZY_CWSZ.CWMC,
ZY_BRRY.BRXM,
ZY_BRRY.BRXZ,
ZY_BQYZ.SYPC,
ZY_BQYZ.YSGH,
ZY_BQYZ.FYFS,
ZY_BQYZ.LSYZ,
ZY_BRRY.ZYHM,
'' AS BH,
'' AS BZXX,YCSL as SL
FROM ZY_BQYZ,
ZY_BRRY,
ZY_CWSZ
WHERE ( ZY_BRRY.ZYH = ZY_BQYZ.ZYH ) AND ( ZY_BRRY.BRCH = ZY_CWSZ.BRCH ) AND
( ZY_BQYZ.SRKS = :al_ksdm ) AND
( ZY_BQYZ.YPLX = 0 ) AND
/*(ZY_BQYZ.JFBZ3) AND (ZY_BQYZ.YPXH=GY_YLSF.FYXH) AND (GY_YLSF.fytj=135) AND*/
(Exists(SELECT 1 FROM GY_YLSF WHERE ZY_BQYZ.JFBZ3 AND ZY_BQYZ.YPXH=GY_YLSF.FYXH AND GY_YLSF.fytj=135) OR
Exists(SELECT 1 FROM BQ_QTYZ WHERE ZY_BQYZ.JFBZ=3 AND ZY_BQYZ.YPXH=BQ_QTYZ.CODE AND BQ_QTYZ.KPLX=1)
) AND
( ( ZY_BQYZ.LSYZ=1 AND ZY_BQYZ.KSSJ>=:adt_begin AND ZY_BQYZ.KSSJ=:adt_begin OR ZY_BQYZ.TZSJ IS NULL ) and ZY_BQYZ.LSBZ1) ) AND
( ZY_BRRY.CYPB = 0 ) AND
( ZY_BQYZ.ZYH = :al_zyh OR (:al_zyh = -1 AND SRKS = :al_ksdm) ) AND
( ZY_BQYZ.YZPB = 0 ) AND
(YSBZ=0 OR YSTJ=1) and(LSYZ =:al_LSYZ or :al_LSYZ = -1)
ORDER BY ZY_BRRY.BRCH,ZY_BQYZ.YZZH,ZY_BQYZ.YJZX DESC,ZY_BQYZ.JLXH
以上的语句在oracle8.0.5客户端,执行检索很慢,而在oracle8.1.7以上的客户端检索又快很多。服务端是10g x64 执行计划如下:
2014-09-15_154838.jpg (48.09 KB, 下载次数: 0)
下载附件
2014-9-15 15:49 上传
|