|
다음 쿼리의 성능을 높일수 있는 방법 부탁드립니다.
데이블 데이타 정보
SCSA 최대 100만건
SCRK 최대 500만건
V_SS 최대 1만건
INSERT INTO SCGT (
keyId,
no,
gno,
bata,
batb,
batc,
batago,
batbgo,
batcgo,
ssNo
)
SELECT
keyId,
no,
gno,
bata,
batb,
batc,
batago,
batbgo,
batcgo,
ssNo
FROM
(
WITH V_SS as
(
SELECT ms.*
FROM
(
SELECT /*+ INDEX_DESC (scn PK_SCSA) */
scn.*,
ROW_NUMBER() OVER (PARTITION BY scn.keyId ORDER BY scn.no DESC) rn
FROM SCSA scn
WHERE EXISTS
(
SELECT /*+ INDEX (sre AK_SCRK13) */
sre.keyId
FROM SCRK sre
WHERE sre.keyId = scn.keyId
AND sre.ttkk = '1'
AND sre.kgkb = '1'
AND sre.stat = '660'
)
) ms
WHERE ms.rn = 1
)
(
SELECT /*+ INDEX (sre PK_SCRK) */
scn.keyId,
scn.no,
scn.gno,
scn.bata,
scn.batb,
scn.batc,
CASE scn.tztksf
WHEN '1' THEN
CASE WHEN sre.tenkbn <> '4' THEN
SUBSTR(NVL(scn3.bata, '00'), 1, 2) ||
SUBSTR(scn.bata, 3, 8) ||
'1' ||
SUBSTR(scn.bata, 12, 1)
ELSE
CASE WHEN SUBSTR(scn.bata, 1, 2) IN ('61', '62','63') THEN
'41' || SUBSTR(scn.bata, 3, 4) || '000000'
ELSE
'42' || SUBSTR(scn.bata, 3, 4) || '000000'
END
END
WHEN '2' THEN
CASE WHEN sre.tenkbn <> '4' THEN
scn.bata
ELSE
CASE WHEN SUBSTR(scn.bata, 1, 2) IN ('61', '62','63') THEN
'41' || SUBSTR(scn.bata, 3, 4) || '000000'
ELSE
'42' || SUBSTR(scn.bata, 3, 4) || '000000'
END
END
ELSE
scn.bata
END batago,
scn.batbgo,
CASE scn.tztksf
WHEN '1' THEN
CASE WHEN sre.tenkbn <> '4' THEN
NVL(scn3.batc, '000000')
ELSE
NVL(scn2.batc, '000000')
END
WHEN '2' THEN
CASE WHEN sre.tenkbn <> '4' THEN
scn.batc
ELSE
CASE WHEN SUBSTR(scn.bata, 1, 2) IN ('61', '62','63') THEN
' '
ELSE
CASE WHEN scn.stkkk = '07' THEN
'000008'
ELSE
'000001'
END
END
END
ELSE
scn.batc
END batcgo,
CASE WHEN scn.tztksf IN ('1', '2') THEN
CASE WHEN sre.tenkbn <> '4' THEN
'2'
ELSE
'3'
END
ELSE
scn.ssNo
END ssNo
FROM SCRK sre,
V_SS scn,
V_SS scn2,
V_SS scn3
WHERE trim(sre.keyId) = scn.keyId
AND sre.ttkk = '1'
AND sre.kgkb = '1'
AND sre.stat = '660'
AND NOT EXISTS
(
SELECT /*+ INDEX (sre2 AK_SCRK06) */
sre2.keyId
FROM SCRK sre2
WHERE sre2.gno = sre.gno
AND sre2.stat NOT IN ('660')
)
AND scn.selecttkeyId = scn2.keyId(+)
AND scn2.tztksf(+) <> '0'
AND scn2.tztksf(+) <> '9'
AND scn.keyId = scn3.selecttkeyId(+)
AND scn.tztksf = scn3.tztksf(+)
AND scn3.keyId(+) <> scn3.selecttkeyId(+)
AND scn3.tztksf(+) <> '0'
AND scn3.tztksf(+) <> '9'
)
UNION ALL
(
SELECT
scn.keyId,
scn.no,
scn.gno,
scn.bata,
scn.batb,
scn.batc,
scn.batago,
scn.batbgo,
scn.batcgo,
scn.ssNo
FROM SCSA scn,
(SELECT /*+ INDEX (sre AK_SCRK13) */
sei.keyId as keyId,
MAX(sei.no) AS max_no
FROM SCSA sei,
SCRK sre
WHERE sre.kgkb IN ('2','7','8','9')
AND sre.ttkk = '1'
AND trim(sre.keyId) = sei.keyId
AND NOT EXISTS
(
SELECT /*+ INDEX (sre2 AK_SCRK06) */
sre2.keyId
FROM SCRK sre2
WHERE sre2.gno = sre.gno
AND sre2.stat NOT IN ('670')
)
AND sre.stat IN ('670')
GROUP BY sei.keyId ) M
WHERE scn.keyId = m.keyId
AND scn.no = m.max_no
)
)
;
|
