꿈꾸는 개발자, DBA 커뮤니티 구루비

대량데이터 조인2

by 구사일생 [SQL Query] 대량 데이터 JOIN 대량건 조회힌트 [2023.08.24 22:30:12]

지난번 문의 드린 후에 다시 한번 다른 경우로 문의 드립니다.

--AAA의 경우
SELECT
Z.IN_CUST_NO
, Z.IN_DATE
, Z.ACCT_NO
, Z.ACCT_SEQ_NO
, Z.LN_CUR_CD
, Z.SEQ_NO
, Z.LN_SUBJ_CD
, Z.PRD_CD
FROM (
       SELECT C.CUST_NO AS IN_CUST_NO
       , C.BASC_DT AS IN_DATE
       , B.ACCT_NO
       , B.ACCT_SEQ_NO
       , B.LN_CUR_CD
       , C.SEQ_NO
       , B.NL_SUBJ_CD
       , B.LN_PRD_CD AS PRD_CD
       , RANK() OVER (PARTITION BY ACCT_NO ORDER BY ACCT_SEQ_NO DESC) RN
       FROM TBL_01 B -- 6억건
       , (
       SELECT DISTINCT T.BASC_DT
       , A.CUST_NO
       , T.SEQ_NO
       FROM TBL_COMM_01 T
       INNER JOIN TBL_02 A
       ON (T.CRYP_RSBZ_REG_NO = A.CRYP_ACNM_NO
       AND T.INQ_USAG_CD = 'R'
       AND T.LN_TRSC_YN = 'Y')
       UNION ALL
       SELECT DISTINCT T.BASC_DT
       , A.CUST_NO
       , T.SEQ_NO
       FROM TBL_COMM_01 T
       INNER JOIN TBL_02_OLD A
       ON (T.CRYP_RSBZ_REG_NO = A.CRYP_ACNM_NO
       AND T.INQ_USAG_CD = 'R'
       AND T.LN_TRSC_YN = 'Y')

       ) C -- 0건 ~ 약1000건
       WHERE B.CUST_NO = C.CUST_NO
AND (B.PRD_DTCLS_CD <> 'A1507' OR TRIM(B.PRD_DTCLS_CD) IS NULL)
AND B.LN_NEW_DT <= C.BASC_DT
AND (B.LN_TRMN_DT > C.BASC_DT OR TRIM(B.LN_TRMN)DT) IS NULL)
AND COALESCE(B.LN_ACCT_TRMN_CD,'00') <> '43'
AND B.LN_DPS_BIZ_DV_CD <> '6'
) Z
;

--BBB의 경우
SELECT
B.CUST_NO AS IN_CUST_NO
, C.BASC_DT
, B.ACCT_NO
, B.ACCT_SEQ_NO
, B.CUR_CD
, C.SEQ_NO
, '' AS LN_SUBJ_CD
, B.RPD_CD
FROM TTBL_01 B --4억건
INNER JOIN TTBL_02 X --1억건
ON (B.ACCT_NO = X.ACCT_NO
AND (((B.SUBJ_CD = '11' AND TRIM(X.PSBK_ISS_SHP_CD) <> '03' AND TRIM(B.PRD_CD) <> '0100018000101')
OR (B.SUBJ_CD = '54' AND TRIM(X.PSBK_ISS_SHP_CD) <> '03')
OR (B.SUBJ_CD = '15' AND (NVL(X.SZMY_SEQ_NO,0) > 1 OR NVL(TRIM(X.PSBK_ISS_SHP_CD),'00') = '01'))
OR (B.SUBJ_CD = '1F' AND (NVL(X.PSBK_ISS_SHP_CD,'00') = '01')
OR (B.SUBJ_CD NOT IN ('11','54','15','1F')))
AND B.SUBJ_CD IN ('31','32','33','34','3A','3B','3C','3D','38'))
, (
SELECT DISTINCT T.BASC_DT
, T.ACCT_NO
, T.SEQ_NO
FROM TBL_COMM_01 T
WHERE T.INQ_USAG_CD = 'R'
AND T.DPS_TRSC_YN = 'Y'
) C -- 0건 ~ 약1000건
, TTBL_03 D -- 5억건
WHERE B.ACCT_NO =D.ACCT_NO
AND C.ACCT_NO =D.CNNT_ACCT_NO
AND D.CONT_CNNT_REL_CD = 'LDM059'
AND CASE WHEN SUBSTR(B.ETC_MGNT_YN_STRN_CD,39,1) = '1' THEN NVL(TRIM(B.FST_NEW_DT), B.NEW_DT)
ELSE B.NEW_DT
END <= C.BASC_DT
AND (B.TRMN_DT > C.BASC_DT
OR TRIM(B.TRMN_DT) IS NULL)
AND (B.ACCT_TRMN_CD NOT IN ('91','92')
OR B.ACT_DV_CD = '1')
AND NOT (B.UNON_CD = '17' AND B.PBNF_TRST_CTBT_METH_CD = '2')

;

AAA 경우와 BBB의 경우가 비슷해 보이는데 쉽게 풀리질 않네요 암튼 잘 모르지만 AAA의 경우 NO_QUERY_TRANSFORMATION 힌트를 주니까 결과가 빠르게

나옵니다. 그런데 BBB의 경우는 전혀 다른 결과가 나오네요

(AAA의 경우는 20초 정도 BBB의 경우는 40분 정도 그리고 AAA의 경우 B.CUST_NO = C.CUST_NO 조인 조건에 인덱스가 없으며

BBB의 경우는 B.ACCT_NO에만 결합 인덱스가 있습니다.ACCT_NO + ACCT_NO_SEQ)

뭐 상세한 내용은 잘 모르고 인터넷을 보고 해 본건데 이유와 아울러 SQL 자체도 좀 정리가 필요해 보이는데 역시나 제 실력은 일천해서 ㅠㅠㅠㅠ

고수 분들의 지도 편달 부탁드립니다.

by 마농 [2023.08.25 13:22:07]

두개 쿼리가 다른 쿼리인데 왜 이 두개 쿼리를 비교하는지 모르겠습니다.
그리고 쿼리의 괄호가 짝이 안맞는데요?

by 구사일생 [2023.08.25 20:51:57]

마음만 급해서 두서 없이 문의 해 혼선을 드렸네요

그리도 사진을 찍어서 집에서 다시 키보드로 치면서 괄호는 하나 빠진듯 합니다.

결론은 BBB를 정리(튜닝) 하고 싶은 겁니다. 어찌 어찌 AAA형태는 인터넷을 뒤져서 어느정도 원하는 시간이 나오다 보니, 비슷한 유형인가 싶어 적용을 해 봤는데 잘 되질 않았습니다.

물론 (업무적으로) 연관은 전혀 없습니다.

그리고 뭐라도 해야 해서 회사에서 힌트를 추가 해 봤습니다.

SELECT /*+ FULL(B) FULL(X) FULL(C) PARALLEL(4) USE_HASH(C)*/
B.CUST_NO AS IN_CUST_NO
, C.BASC_DT
, B.ACCT_NO
, B.ACCT_SEQ_NO
, B.CUR_CD
, C.SEQ_NO
, '' AS LN_SUBJ_CD
, B.RPD_CD
FROM TTBL_01 B --4억건
INNER JOIN TTBL_02 X --1억건
ON (B.ACCT_NO = X.ACCT_NO
AND (
(
(B.SUBJ_CD = '11' AND TRIM(X.PSBK_ISS_SHP_CD) <> '03' AND TRIM(B.PRD_CD) <> '0100018000101')
OR (B.SUBJ_CD = '54' AND TRIM(X.PSBK_ISS_SHP_CD) <> '03')
OR (B.SUBJ_CD = '15' AND (NVL(X.SZMY_SEQ_NO,0) > 1 OR NVL(TRIM(X.PSBK_ISS_SHP_CD),'00') = '01'))
OR (B.SUBJ_CD = '1F' AND (NVL(X.PSBK_ISS_SHP_CD,'00') = '01') OR (B.SUBJ_CD NOT IN ('11','54','15','1F'))
)
AND B.SUBJ_CD IN ('31','32','33','34','3A','3B','3C','3D','38')
)
)
, (
SELECT /*+ FULL(T) PARALLEL(T 4)*/
DISTINCT T.BASC_DT
, T.ACCT_NO
, T.SEQ_NO
FROM TBL_COMM_01 T
WHERE T.INQ_USAG_CD = 'R'
AND T.DPS_TRSC_YN = 'Y'
) C -- 0건 ~ 약1000건
, TTBL_03 D -- 5억건
WHERE B.ACCT_NO =D.ACCT_NO
AND C.ACCT_NO =D.CNNT_ACCT_NO
AND D.CONT_CNNT_REL_CD = 'LDM059'
AND CASE WHEN SUBSTR(B.ETC_MGNT_YN_STRN_CD,39,1) = '1' THEN NVL(TRIM(B.FST_NEW_DT), B.NEW_DT)
ELSE B.NEW_DT
END <= C.BASC_DT
AND (B.TRMN_DT > C.BASC_DT
OR TRIM(B.TRMN_DT) IS NULL)
AND (B.ACCT_TRMN_CD NOT IN ('91','92')
OR B.ACT_DV_CD = '1')
AND NOT (B.UNON_CD = '17' AND B.PBNF_TRST_CTBT_METH_CD = '2')

처음에는 4건의 결과가 나오는데 약 45분 정도 걸렸으나 위 처럼 하여 약 5분정도 걸렸습니다.

그래도 좀더 단축이 될까 싶어 올립니다.(예전 SYBASE에서는 30초 걸림)

다시 한번 내용 확인 부탁드립니다.

by 마농 [2023.08.25 22:47:15]

괄호가 아직도 이상합니다. 여전히 짝이 안맞고요
OR 조건 부분의 괄호가 부정확합니다. 조건의 의미가 모순입니다.
TRIM 이나 NVL, DISTINCT 사용이 적절한지도 의문입니다. 안써도 되는 걸 쓴 것은 아닌지?

by 구사일생 [2023.08.27 10:44:33]

사진을 보고 옮겨 적으면서 좀더 꼼꼼히 봤어야 했는데 오른쪽 괄호가 또 하나가 빠졌네요 죄송합니다.

한참을 찾았네요

그리고 "TRIM 이나 NVL, DISTINCT 사용이 적절한지도 의문입니다 "

==> sysbase 에 있던 문장들을 그대로 oracle로 변경하는 작업을 하고 있습니다. 해서 아직 업무적으로 어떤 의미로 사용이 되어 진 건지 아직 정확히 모르고 기계적으로(??) 변경만 하고 있어서요 ㅠㅠ.

답을 주기 위해서 이렇게 까지 해주셔도 매번 감동 입니다.

--BBB의 경우
SELECT /*+ FULL(B) FULL(X) FULL(C) PARALLEL(4) USE_HASH(C)*/
B.CUST_NO AS IN_CUST_NO
, C.BASC_DT
, B.ACCT_NO
, B.ACCT_SEQ_NO
, B.CUR_CD
, C.SEQ_NO
, '' AS LN_SUBJ_CD
, B.RPD_CD
FROM TTBL_01 B --4억건
INNER JOIN TTBL_02 X --1억건

------------------------------------------------------------------------------------------------------------
ON (B.ACCT_NO = X.ACCT_NO
AND (
       (
       (B.SUBJ_CD = '11' AND TRIM(X.PSBK_ISS_SHP_CD) <> '03' AND TRIM(B.PRD_CD) <> '0100018000101')
       OR (B.SUBJ_CD = '54' AND TRIM(X.PSBK_ISS_SHP_CD) <> '03')
       OR (B.SUBJ_CD = '15' AND (NVL(X.SZMY_SEQ_NO,0) > 1 OR NVL(TRIM(X.PSBK_ISS_SHP_CD),'00') = '01'))
       OR (B.SUBJ_CD = '1F' AND (NVL(X.PSBK_ISS_SHP_CD,'00') = '01') )    --<-- 괄호 추가
       OR (B.SUBJ_CD NOT IN ('11','54','15','1F'))
       )
AND B.SUBJ_CD IN ('31','32','33','34','3A','3B','3C','3D','38')
)
)

------------------------------------------------------------------------------------------------------------
, (
SELECT /*+ FULL(T) PARALLEL(T 4)*/
DISTINCT T.BASC_DT
, T.ACCT_NO
, T.SEQ_NO
FROM TBL_COMM_01 T
WHERE T.INQ_USAG_CD = 'R'
AND T.DPS_TRSC_YN = 'Y'
) C -- 0건 ~ 약1000건
, TTBL_03 D -- 5억건
WHERE B.ACCT_NO =D.ACCT_NO
AND C.ACCT_NO =D.CNNT_ACCT_NO
AND D.CONT_CNNT_REL_CD = 'LDM059'
AND CASE WHEN SUBSTR(B.ETC_MGNT_YN_STRN_CD,39,1) = '1' THEN NVL(TRIM(B.FST_NEW_DT), B.NEW_DT)
ELSE B.NEW_DT
END <= C.BASC_DT
AND (B.TRMN_DT > C.BASC_DT
OR TRIM(B.TRMN_DT) IS NULL)
AND (B.ACCT_TRMN_CD NOT IN ('91','92')
OR B.ACT_DV_CD = '1')
AND NOT (B.UNON_CD = '17' AND B.PBNF_TRST_CTBT_METH_CD = '2')

by 마농 [2023.08.27 12:00:05]

괄호는 맞춰 주셨는데...
OR 조건의 의미가 상충되는 부분이 있습니다.
아래쪽 AND 조건인 AND B.SUBJ_CD IN ('31','32','33','34','3A','3B','3C','3D','38') 을 만족하려면
위쪽 OR 조건 안에 B.SUBJ_CD = '11', '54', '15', '1F' 조건은 만족할 수 없습니다.
NOT IN 조건만 만족 됩니다.
이 조건이 정말 맞다면? 위 쪽 OR 부분은 없어도 될 조건입니다.
아래쪽 AND 조건인 AND B.SUBJ_CD IN ('31','32','33','34','3A','3B','3C','3D','38') 만 남기면 됩니다.

by 구사일생 [2023.08.27 15:42:44]

혹시나 하고 로그인 했는데 일요일에도 답을 주셨네요

주신 답변은 내일 회사에 가서 적용해보고 결과 올리겠습니다.

감사합니다.

by 구사일생 [2023.08.31 22:11:00]

계속되는 야근으로 이제야 결과를 올립니다.

말씀하신 내용을 고객과 협의 후 프로그램에서 제거 하기로 하였습니다.

물론 처음의 의도인 속도 개선에 크게 변화가 있는 건 아니었지만 이것도 상당한 수확이네요

또 , 궁금한 내용이 있으면 올리고 배우고 더욱 정진 할게요 감사합니다.

by 마농 [2023.09.01 11:28:21]

불필요하고 비효율적인 부분을 하나씩 제거해 나가 보세요.
1. 불필요한 OR 조건 제거
2. DISTINCT 는 정말 필요한 것인가?
3. TRIM 은 정말 필요한가? 공백이 들어간 자료가 있어서 결과에 영향을 주는가?
4. x 테이블은 조인조건으로만 사용이 되었고
- Select 나 Where 절에서 전혀 사용이 안되고 있는데?
- x 테이블을 조인할 필요가 있는가?

by 구사일생 [2023.09.03 15:42:46]

생각해 보니 " x 테이블을 조인할 필요가 있는가? " 이 부분에 대한 고민을 좀더 해 봐야 겠네요

이것도 함 적용해보고 건수 차이가 나는지 같은지 확인 해 보고 결과 올릴게요 ~~ 계속 이렇게 배워 갑니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변