데이터베이스 질문과 답변
SELECT A.FCLTY_KND_CODE,
FN_GET_CMMN_CODENM (A.FCLTY_KND_CODE) AS FCLTY_KND_NM,
NVL (B.A, 0) AS A,
NVL (C.B, 0) AS B,
NVL (D.C, 0) AS C,
NVL (E.D, 0) AS D,
NVL (F.E, 0) AS E,
NVL (G.F, 0) AS F,
NVL (H.G, 0) AS G,
NVL (I.H, 0) AS H,
NVL (J.I, 0) AS I,
NVL (K.J, 0) AS J,
NVL (L.K, 0) AS K,
NVL (M.L, 0) AS L,
NVL (N.M, 0) AS M,
NVL (O.N, 0) AS N,
NVL (P.O, 0) AS O,
NVL (Q.P, 0) AS P,
NVL (R.Q, 0) AS Q,
NVL (S.R, 0) AS R,
NVL (T.S, 0) AS S,
NVL (B2.A, 0) AS A2,
NVL (C2.B, 0) AS B2,
NVL (D2.C, 0) AS C2,
NVL (E2.D, 0) AS D2,
NVL (F2.E, 0) AS E2,
NVL (G2.F, 0) AS F2,
NVL (H2.G, 0) AS G2,
NVL (I2.H, 0) AS H2,
NVL (J2.I, 0) AS I2,
NVL (K2.J, 0) AS J2,
NVL (L2.K, 0) AS K2,
NVL (M2.L, 0) AS L2,
NVL (N2.M, 0) AS M2,
NVL (O2.N, 0) AS N2,
NVL (P2.O, 0) AS O2,
NVL (Q2.P, 0) AS P2,
NVL (R2.Q, 0) AS Q2,
NVL (S2.R, 0) AS R2,
NVL (T2.S, 0) AS S2
FROM ( SELECT DECODE (ROWNUM,
1, 'PM15',
2, 'PM16',
3, 'PM17',
4, 'PM18')
AS FCLTY_KND_CODE
FROM DUAL
CONNECT BY LEVEL <= 4) A,
( SELECT COUNT (*) AS A, A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532121'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) B,
( SELECT COUNT (*) AS B,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532098'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) C,
( SELECT COUNT (*) AS C,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532074'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) D,
( SELECT COUNT (*) AS D,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532056'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) E,
( SELECT COUNT (*) AS E,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532255'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) F,
( SELECT COUNT (*) AS F,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532281'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) G,
( SELECT COUNT (*) AS G,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532222'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) H,
( SELECT COUNT (*) AS H,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532199'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) I,
( SELECT COUNT (*) AS I,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532170'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) J,
( SELECT COUNT (*) AS J,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532376'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) K,
( SELECT COUNT (*) AS K,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532357'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) L,
( SELECT COUNT (*) AS L,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532329'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) M,
( SELECT COUNT (*) AS M,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532304'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) N,
( SELECT COUNT (*) AS N,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532466'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) O,
( SELECT COUNT (*) AS O,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532609'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) P,
( SELECT COUNT (*) AS P,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532440'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) Q,
( SELECT COUNT (*) AS Q,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532418'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) R,
( SELECT COUNT (*) AS R,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532508'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) S,
( SELECT COUNT (*) AS S,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532530'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY<300
GROUP BY A.FCLTY_KND_CODE) T,
( SELECT COUNT (*) AS A, A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532121'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) B2,
( SELECT COUNT (*) AS B,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532098'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) C2,
( SELECT COUNT (*) AS C,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532074'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) D2,
( SELECT COUNT (*) AS D,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532056'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) E2,
( SELECT COUNT (*) AS E,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532255'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) F2,
( SELECT COUNT (*) AS F,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532281'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) G2,
( SELECT COUNT (*) AS G,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532222'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) H2,
( SELECT COUNT (*) AS H,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532199'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) I2,
( SELECT COUNT (*) AS I,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532170'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) J2,
( SELECT COUNT (*) AS J,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532376'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) K2,
( SELECT COUNT (*) AS K,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532357'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) L2,
( SELECT COUNT (*) AS L,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532329'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) M2,
( SELECT COUNT (*) AS M,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532304'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) N2,
( SELECT COUNT (*) AS N,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532466'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) O2,
( SELECT COUNT (*) AS O,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532609'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) P2,
( SELECT COUNT (*) AS P,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532440'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) Q2,
( SELECT COUNT (*) AS Q,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532418'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) R2,
( SELECT COUNT (*) AS R,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532508'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) S2,
( SELECT COUNT (*) AS S,
A.FCLTY_KND_CODE
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
AND A.FCLTY_KND_CODE IN ('PM15', 'PM16', 'PM17', 'PM18')
AND A.NMPA_OFE_CODE = '1532530'
AND A.BSN_SE_CODE = ?
AND A.STTEMNT_SE_CODE = ? AND B.TOT_CPCTY>=300
GROUP BY A.FCLTY_KND_CODE) T2
WHERE A.FCLTY_KND_CODE = B.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = C.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = D.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = E.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = F.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = G.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = H.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = I.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = J.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = K.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = L.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = M.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = N.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = O.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = P.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = Q.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = R.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = S.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = T.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = B2.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = C2.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = D2.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = E2.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = F2.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = G2.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = H2.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = I2.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = J2.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = K2.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = L2.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = M2.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = N2.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = O2.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = P2.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = Q2.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = R2.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = S2.FCLTY_KND_CODE(+)
AND A.FCLTY_KND_CODE = T2.FCLTY_KND_CODE(+)
------------------------------------------------------------------------------------------------------------------------------------------------------------
데이터가 없는데(1~2건) 쿼리가 20초가 걸리는상황입니다.
실행계획은 너무 길어 일부분만 가져왔습니다.
어떤식으로 튜닝을 해야 좋을지 잘모르겠습니다...
도와주시면 감사하겠습니다.
by 우리집아찌
[2019.01.25 12:52:45]
/*
동일한 부분은 하나의 테이블로 만드셔서
JOIN 걸어보세요.
*/
(
SELECT A.FCLTY_KND_CODE
, COUNT(CASE WHEN A.NMPA_OFE_CODE = '1532121' AND B.TOT_CPCTY<300 THEN 1 END ) AS A
, COUNT(CASE WHEN A.NMPA_OFE_CODE = '1532098' AND B.TOT_CPCTY<300 THEN 1 END ) AS B
, COUNT(CASE WHEN A.NMPA_OFE_CODE = '1532074' AND B.TOT_CPCTY<300 THEN 1 END ) AS C
, COUNT(CASE WHEN A.NMPA_OFE_CODE = '1532056' AND B.TOT_CPCTY<300 THEN 1 END ) AS D
, COUNT(CASE WHEN A.NMPA_OFE_CODE = '1532255' AND B.TOT_CPCTY<300 THEN 1 END ) AS E
.
.
.
, COUNT(CASE WHEN A.NMPA_OFE_CODE = '1532530' AND B.TOT_CPCTY>=300THEN 1 END ) AS S
FROM TPM_FCLTY_INFO A, TPM_HOLD_STRE_FCLTY B
WHERE A.FCLTY_SN = B.FCLTY_SN(+)
GROUP BY A.FCLTY_KND_CODE
)
by 초보개발자입니다!
[2019.01.27 15:49:21]
좋은 답변 감사합니다 ^^
by 마농
[2019.01.25 14:08:09]
1. 공통된 개별 서브쿼리 통합(아찌님 쿼리)
- 공통 조건은 Where 절에
- 개별 조건은 Select 절의 Count 안에서 Case 나 Decode 문으로
2. 잘못된 아우터 조인 사용 제거.
- b 에 조건을 주고 있으므로 아우터 조인 불필요 할 듯
3. 코드테이블 집합 a 제거
- 1번 처럼 변경 후에는 a 집합 불필요 할 듯.
SELECT a.fclty_knd_code
, fn_get_cmmn_codenm(a.fclty_knd_code) fclty_knd_nm
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532121' AND b.tot_cpcty < 300 THEN 1 END) a
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532098' AND b.tot_cpcty < 300 THEN 1 END) b
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532074' AND b.tot_cpcty < 300 THEN 1 END) c
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532056' AND b.tot_cpcty < 300 THEN 1 END) d
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532255' AND b.tot_cpcty < 300 THEN 1 END) e
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532281' AND b.tot_cpcty < 300 THEN 1 END) f
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532222' AND b.tot_cpcty < 300 THEN 1 END) g
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532199' AND b.tot_cpcty < 300 THEN 1 END) h
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532170' AND b.tot_cpcty < 300 THEN 1 END) i
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532376' AND b.tot_cpcty < 300 THEN 1 END) j
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532357' AND b.tot_cpcty < 300 THEN 1 END) k
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532329' AND b.tot_cpcty < 300 THEN 1 END) l
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532304' AND b.tot_cpcty < 300 THEN 1 END) m
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532466' AND b.tot_cpcty < 300 THEN 1 END) n
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532609' AND b.tot_cpcty < 300 THEN 1 END) o
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532440' AND b.tot_cpcty < 300 THEN 1 END) p
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532418' AND b.tot_cpcty < 300 THEN 1 END) q
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532508' AND b.tot_cpcty < 300 THEN 1 END) r
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532530' AND b.tot_cpcty < 300 THEN 1 END) s
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532121' AND b.tot_cpcty >= 300 THEN 1 END) a2
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532098' AND b.tot_cpcty >= 300 THEN 1 END) b2
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532074' AND b.tot_cpcty >= 300 THEN 1 END) c2
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532056' AND b.tot_cpcty >= 300 THEN 1 END) d2
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532255' AND b.tot_cpcty >= 300 THEN 1 END) e2
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532281' AND b.tot_cpcty >= 300 THEN 1 END) f2
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532222' AND b.tot_cpcty >= 300 THEN 1 END) g2
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532199' AND b.tot_cpcty >= 300 THEN 1 END) h2
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532170' AND b.tot_cpcty >= 300 THEN 1 END) i2
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532376' AND b.tot_cpcty >= 300 THEN 1 END) j2
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532357' AND b.tot_cpcty >= 300 THEN 1 END) k2
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532329' AND b.tot_cpcty >= 300 THEN 1 END) l2
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532304' AND b.tot_cpcty >= 300 THEN 1 END) m2
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532466' AND b.tot_cpcty >= 300 THEN 1 END) n2
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532609' AND b.tot_cpcty >= 300 THEN 1 END) o2
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532440' AND b.tot_cpcty >= 300 THEN 1 END) p2
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532418' AND b.tot_cpcty >= 300 THEN 1 END) q2
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532508' AND b.tot_cpcty >= 300 THEN 1 END) r2
, COUNT(CASE WHEN a.nmpa_ofe_code = '1532530' AND b.tot_cpcty >= 300 THEN 1 END) s2
FROM tpm_fclty_info a
, tpm_hold_stre_fclty b
WHERE a.fclty_sn = b.fclty_sn
AND a.fclty_knd_code IN ('PM15', 'PM16', 'PM17', 'PM18')
AND a.bsn_se_code = ?
AND a.sttemnt_se_code = ?
GROUP BY a.fclty_knd_code
ORDER BY a.fclty_knd_code
;
by 초보개발자입니다!
[2019.01.27 15:50:47]
좋은 답변 감사합니다 ^^
주신 쿼리에서 좀더 보강하여 해결하였습니다 ^^ 감사합니다
) 버튼을 클릭하여 작성 하시면 됩니다.