데이터베이스 질문과 답변
Table1 rowcount : 약 500만건
Table1의 index1 : YYYY
Table1의 index2 : YMD
Table2 rowcount : 약 2,500만건
Table2의 index1 : YYYY
Table2의 index2 : YMD
#환경 : 전자정부프레임워크
운영서버 : 제우스(7버전), java 1.7, 오라클 10g
로컬 : 톰캣8, java 1.8, 오라클 10g(운영db 연결)
---------------------------------------------------------------------------
현상 : - sql tool 에서 1초 이내(plan 에서 index scan)
- 운영에서 조회 시 약 (7~10여 초)
- 로컬(톰캣)에서 운영DB에 연결하여 조회하면 1초이내 조회
- ibatis(mybatis) 사용이 원인일까 싶어 이를 사용하지 않고 하드코딩하여 테스트 중
- ibatis(mybatis) 사용하지않고 하드코딩하여 db연결 및 쿼리 결과를 이용합니다.
SELECT year, ymd, pYmd, bAmt, inAmt, outAmt, totInAmt, totOutAmt
FROM (
SELECT /*+ index(A Table2_IDX2) index(B Table1_IDX2) */
NVL2(B.YYYY, B.YYYY, '2023') as year
, B.YMD as ymd
, B.YMD as pYmd
, 100000 as bAmt -- 값 고정
, nvl(SUM(A.AMT),0) as inAmt
, nvl(SUM(B.AMT),0) as outAmt
, 20000 as totInAmt -- 값 고정
, 30000 as totOutAmt -- 값 고정
FROM Table1 A, Table2 B
WHERE B.YYYY = '2023'
AND B.YYYY = A.YYYY
AND B.YMD = A.YMD(+)
AND B.YMD BETWEEN REPLACE('2023-12-01', '-', '') AND REPLACE('2023-12-31', '-', '')
AND A.YMD BETWEEN REPLACE('2023-12-01', '-', '') AND REPLACE('2023-12-31', '-', '')
GROUP BY B.YYYY, B.YMD
)
ORDER BY pYmd ASC
# 복잡한 쿼리가 아니라서 힌트도 사용해보고 했지만 결과는 위 현상과 유사한 결과입니다.(힌트 없어도 결과는 비슷합니다.)
현재 was 환경을 점검요청 또는 스프링 환경 설정변경을 하기전에 한번 더 혹시 쿼리상 다른 원인이 있지 않을까 해서 질문 드립니다. - (jdbc 드라이버 교체해볼 예정)
(+) 가 왜 사용된지 모르겠구요. -> 없는거나 마찬가지
NVL2 는 또 왜 사용된지 모르겠구요. -> NVL 의미처럼 썻는데. 널이 있을리도 없을텐데?
실제는 바인드 변수 사용하는데, 테스트는 상수값으로 테스트 한 것 은 아닌지?
ymd 로 조인한 후 ymd 로 그룹바이 하는데
각각 SUM 을 한다면?
(1:다) 또는 (다:다) 관계로 의심되는데
(1:다) 또는 (다:다) 관계라면 이런식의 쿼리는 잘못된 결과가 나오게 됩니다.
각각을 그룹바이 SUM 하여 합치는 방식을 취해야 할 듯 하네요.
인덱스는 yyyy 타도 되는데 굳이 ymd 로 힌트 주셨네요?
ymd 조건은 굳이 필요한가요? yyyy 조건과 동일하지 않나요?
SELECT yyyy, ymd
, SUM(inAmt ) inAmt
, SUM(outAmt) outAmt
FROM (SELECT yyyy, ymd
, SUM(amt) inAmt
, 0 outAmt
FROM table1
WHERE yyyy = '2023'
GROUP BY yyyy, ymd
UNION ALL
SELECT yyyy, ymd
, 0 inAmt
, SUM(amt) outAmt
FROM table2
WHERE yyyy = '2023'
GROUP BY yyyy, ymd
)
GROUP BY yyyy, ymd
ORDER BY yyyy, ymd
;
답변 감사합니다.
(+) 값은 같은 일자 즉 Table2의 B.YMD에 B.AMT 있지만 Table1의 A.YMD에 A.AMT 값이 없어도 outAmt 값이 기본적으로 나오게 처리하기 위함입니다.
바인드 변수 영향이 있을까 싶어 테스트로 만든 부분을 실제 운영에 적용하였습니다(상수값 사용).
인덱스는 yyyy 타도 되는데 굳이 ymd 로 힌트 주셨네요? 이부분은 YYYY 로도 힌트를 사용했는데도 별 차이가 없어서 그대로 두었습니다.(힌트 사용하지 않아도 별 차이가 없었습니다.)
이해가 안되는 부분이 운영db 연결하여 로컬(톰캣)에서 조회(1초 이내) 할 때와 운영에서 조회 때 차이가 10여초 차이 나는게 이해가 잘 안되어 원인을 잡기 위해 여러가지 테스트 중입니다.
(was(제우스 : 톰캣) 외엔 소스 및 db는 완전 일치)
(1:다) 또는 (다:다) 이부분이 의심되신다고 하시면 이부분을 고려해 재작성하여 확인해보겠습니다.
저도 지금 마농님과 유사하게 쿼리 수정하고 있었습니다. 감사합니다.
(+) 는 잘못 사용하셨습니다.
- 올바른 사용법이 아닙니다.
1:다 조인이라면? SUM 부분은 잘못 사용하셨습니다.
- 1 쪽 금액이 부풀려 지게 됩니다.
- 1 쪽 금액은 SUM 이 아닌 그룹바이 항목으로 사용해야 합니다.
다:다 조인이라면? 잘못된 조인입니다.
- 양 쪽 금액이 모두 부풀려 지게 됩니다.
넵. 감사합니다.
그 동안 별 생각없이 해 왔던대로 작성하여 사용했었는데 오늘 마농님 덕분에 또 하나 배워갑니다.
평소 SQL 작업 시 인덱스, 실행계획, 힌트, 결과값 정도의 얇은 지식으로 작성하였는데 많은 생각을 가지게하는 배움이었습니다.
다시 한번 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.