oracle如何优化多表连接,多表连接SQL优化如何处理

类似：

WITH T_V

AS

(SELECT DISTINCT SUBSTR(yg5.CLASS_FLAG, 1, 9) AS CLASS_FLAG,

yg5.ZQID_jk005,

yg5.sectname,

p205.secid_zj205

FROM TABLE(arr_repdate) ARR1,

jk005 yg5,zj205 p205

WHERE ARR1.COLUMN_VALUE = SUBSTR(yg5.CLASS_FLAG, 1, 9)

AND yg5.ISVALID = 1

AND yg5.zQID_jk005 = p205.f019v_zj205

AND p205.isvalid = 1

)

SELECT /*+ no_merge(v) no_merge(v209)*/

TO_CHAR(v.jyDATE, 'YYYYMMDD') AS j00118_F001,

v209.F006N_jy209 AS j00118_F002,

CASE

WHEN IV_pejsff = '0001' THEN

TO_NUMBER(v.ep_tm)

WHEN IV_pejsff = '0002' THEN

TO_NUMBER(Decode(v.tm_JLR, 0, NULL, v.SZ1 / v.tm_JLR))

END AS j00118_F003,

CASE

WHEN IV_pejsff = '00003' THEN

TO_NUMBER(v.SJL)

WHEN IV_pejsff = '0009' THEN

TO_NUMBER(Decode(v.GDQY, 0, NULL, v.SZ2 / v.GDQY))

END AS j00118_F004,

CASE

WHEN IV_pejsff = '0004' THEN

TO_NUMBER(v.SYL)

WHEN IV_pejsff = '0005' THEN

TO_NUMBER(Decode(v.MGSJLR, 0, NULL, v.SZ2 / v.MGSJLR))

END AS j00118_F005,

v.CLASS_FLAG AS scbk

FROM (SELECT /*+ no_merge(v)*/

v.CLASS_FLAG,

v.ZQID_jk005,

v.jyDATE,

SUM(v.sz) AS sz,

SUM(Decode(v.tm_JLR,NULL,0,v.sz)) AS sz1,

SUM(Decode(v.GDQY,NULL,0,v.sz)) AS sz2,

SUM(v.tm_JLR) AS tm_JLR,

SUM(v.GDQY) AS GDQY,

SUM(v.MGSJLR) AS MGSJLR,

AVG(CASE WHEN IV_tcgz = 'xztc' AND IV_tcfs <> '0' AND v.SJL >= 0 AND IV_tcdydcdz = '0' THEN

v.SJL

WHEN IV_tcgz = 'xztc' AND IV_tcfs <> '0' AND v.SJL >= 0 AND IV_tcdydcdz <> '0' THEN

CASE WHEN IV_zdz >= 0 AND v.SJL <= IV_zdz  THEN

v.SJL

WHEN (IV_zdz < 0 OR IV_zdz IS NULL) AND v.SJL >= 0 THEN

v.SJL

END

WHEN IV_tcgz = 'xztc' AND IV_tcfs = '0' AND v.SJL <= IV_zdz AND IV_tcdydcdz <> '0' THEN

v.SJL

WHEN IV_tcgz = 'btc' OR IV_pejsff = 'ztf' THEN

v.SJL

ELSE

NULL

END

) AS SJL,

AVG(CASE WHEN IV_tcgz = 'xztc' AND IV_tcfs <> '0' AND v.SYL >= 0 AND IV_tcdydcdz = '0' THEN

v.SYL

WHEN IV_tcgz = 'xztc' AND IV_tcfs <> '0' AND v.SYL >= 0 AND IV_tcdydcdz <> '0' THEN

CASE WHEN IV_zdz >= 0 AND v.SYL <= IV_zdz THEN

v.SYL

WHEN (IV_zdz < 0 OR IV_zdz IS NULL) AND v.SYL >= 0 THEN

v.SYL

END

WHEN IV_tcgz = 'xztc' AND IV_tcfs = '0' AND v.SYL <= IV_zdz AND IV_tcdydcdz <> '0' THEN

v.SYL

WHEN IV_tcgz = 'btc' OR IV_pejsff = 'ztf' THEN

v.SYL

ELSE

NULL

END

) AS SYL,

AVG(CASE WHEN IV_tcgz = 'xztc' AND IV_tcfs <> '0' AND v.ep_tm >= 0 AND IV_tcdydcdz = '0' THEN

v.ep_tm

WHEN IV_tcgz = 'xztc' AND IV_tcfs <> '0' AND v.ep_tm >= 0 AND IV_tcdydcdz <> '0' THEN

CASE WHEN IV_zdz >= 0 AND v.ep_tm <= IV_zdz THEN

v.ep_tm

WHEN (IV_zdz < 0 OR IV_zdz IS NULL) AND v.ep_tm >= 0 THEN

v.ep_tm

END

WHEN IV_tcgz = 'xztc' AND IV_tcfs = '0' AND v.ep_tm <= IV_zdz AND IV_tcdydcdz <> '0' THEN

v.ep_tm

WHEN IV_tcgz = 'btc' OR IV_pejsff = 'ztf' THEN

v.ep_tm

ELSE

NULL

END

) AS ep_tm

FROM (SELECT /*+ no_merge(vv)*/

vv.CLASS_FLAG,

vv.ZQID_jk005,

vv.sectname,

vv.jyDATE,

j207.f003n_jt207 AS sz,

j207.f001n_jt207 AS tm_JLR,

j207.f002n_jt207 AS GDQY,

j207.f004n_jt207 AS MGSJLR,

Decode(j207.f001n_jt207,

0,

0,

j207.f003n_jt207 / j207.f001n_jt207) AS ep_tm,

Decode(j207.f002n_jt207,

0,

0,

j207.f003n_jt207 / j207.f002n_jt207) AS SJL,

Decode(j207.f004n_jt207,

0,

0,

j207.f003n_jt207 / j207.f004n_jt207) AS SYL

FROM (SELECT /*+ no_merge(v009) no_merge(v4) use_nl(v009,v4)*/

v4.CLASS_FLAG,

v4.ZQID_jk005,

v4.sectname,

v009.enddate_w,

v4.f011v_sk001,

Decode(SIGN(v009.enddate - v_enddate),

1,

v_enddate,

v009.enddate) AS jyDATE

FROM (SELECT TRUNC(p9.enddate, 'd') enddate_w,

MAX(p9.enddate) enddate

FROM p009 p9

WHERE p9.jymarket_pub009 = '212001'

AND p9.f001 = '1'

AND p9.isvalid = '1'

AND p9.enddate >=

TO_DATE(IV_startdate, 'YYYYMMDD')

AND p9.enddate <= v_enddate

GROUP BY TRUNC(p9.enddate, 'd')

) v009,

(SELECT v.CLASS_FLAG,

v.ZQID_jk005,

v.sectname,

s1.f011v_sk001,

i4.startdate,

i4.f003

FROM in002 i2,in004 i4,sk001 s1,T_V v

WHERE i2.isvalid = '1'

AND i4.isvalid = 1

AND i2.seq = i4.vseq

AND i4.f001v_ind004 = s1.f011v_sk001

AND s1.isvalid = '1'

AND i2.secid_ind002 = v.secid_zj205

) v4

WHERE v4.startdate <= v009.enddate - 1

AND (v4.f003 >= v009.enddate OR v4.f003 IS NULL)

) vv,

jt207 j207

WHERE j207.enddate_jt207(+) >= TO_DATE(IV_startdate, 'YYYYMMDD')

AND j207.enddate_jt207(+) <= v_enddate

AND j207.zqid_jt207(+) = vv.f011v_sk001

AND TRUNC(j207.enddate_jt207(+), 'd') = vv.enddate_w

AND j207.isvalid(+) = 1) v

WHERE (IV_tcgz = 'xztc' AND IV_tcfs <> '0' AND

(v.ep_tm >= 0 OR v.SJL >= 0  OR v.SYL >= 0) AND IV_tcdydcdz <> '0' AND

(v.ep_tm <= IV_zdz OR v.SJL <= IV_zdz OR v.SYL <= IV_zdz))

OR (IV_tcgz = 'xztc' AND IV_tcfs <> '0' AND

(v.ep_tm >= 0 OR v.SJL >= 0  OR v.SYL >= 0) AND IV_tcdydcdz = '0')

OR (IV_tcgz = 'xztc' AND IV_tcfs = '0' AND

IV_tcdydcdz <> '0' AND (v.ep_tm <= IV_zdz OR v.SJL <= IV_zdz  OR v.SYL <= IV_zdz)

)

OR (IV_tcgz = 'xztc' AND IV_tcfs = '0' AND

IV_tcdydcdz = '0')

OR IV_tcgz = 'btc'

GROUP BY v.CLASS_FLAG, v.ZQID_jk005, v.jyDATE) v,

(SELECT t209.f031v_jy209,

t209.jydate_jy209,

t209.f006n_jy209

FROM jy209 t209

WHERE t209.isvalid = '1'

AND t209.jydate_jy209 >=

TO_DATE(IV_startdate, 'YYYYMMDD')

AND t209.jydate_jy209 <= v_enddate) v209 =

WHERE v.ZQID_jk005 = v209.f031v_jy209(+)

AND v.jyDATE = v209.jydate_jy209(+);