데이터베이스 질문과 답변
안녕하세요. 연계 배치 실행중 잦은 DB LOCK때문에 머리가 아픈 자바 개발자입니다.
도움요청점 드리겠습니다.
업무테이블 : A 테이블 (ROW갯수 1,300,000건, PK : a, b, 유효시작일시, 유효종료일시)
연계테이블 : B 테이블 (ROW갯수 50,000건 PK: c,d)
조회쿼리
SELECT *
FROM A
WHERE 유효종료일시 > to_date('99991231')
AND (a,b) IN ( SELECT c, d
FROM B
AND ORDER BY c, d
OFFSET 0 ROWS FETCH FIRST 5000 ROWS ONLY)
이렇게 조회하면 실행시간이 50~60초 정도 걸립니다 .
PLAN을 봐도 INDEX RANGE SCAN 을 타고 있는걸로 보이는데 속도가 왜 저지경인지 모르겠네요.
더 큰 테이블 보기전에 작은 테이블 먼저 보고있는데 머가 문제인지를 모르겠습니다.
고수님들 조언점 부탁드리겠습니다.
ㅇ 위 내용만 봐서는 잘???
1) 유효종료일 > to_date('99991231') : 이부분에 대한 해당 되는 자료가 있나요?
2) 유효종료일 ( a, b ) 부분의 b에 해당 되는 index key 이면, a : 유효시작일 부분의 where 조건이 없으면 그냥 full scan
3) 그리고, where 조건에 ( a, b ) 부분에서도 ( select c , d from B 부분의 order by ??? , 그리고, 해당 Join 부분도 없으면??? )
최악의 SQL
이유 : A 테이블 ( 150건에 ) => where ( a, b) in 조건에 의해 B 테이블이 5만 건 전부 조인 되는 것임
즉 : 실행 150만건 * 5만 = 150억 번 조회 하는
offset fetch first 5000 rows only == 이부분으 SQL 외 하셨는지 이해가 ????
해당 5000 건 끊어서 조회 하여 없으며, ?
다음에 나오면 ???
< 제가 만약 상기 부분의 처리 한다 면 >
1) A table : b 부분의 index 를 하나 생성 한다.
2) A table , B table 간의 조인 기준이 key ( a , b ) , ( c , d ) 주 key 이면,
( select c, d
from B
where B.c =A.a and B.d = A.b
......................................... )
이렇게 처리 하여,
B table 접근을 최호화 기본이 됩니다.
최종 B table 해당 존재여부 확인 하는 subquery 원하신다면,
exists 문을 이용하시기 바랍니다.
select a , b
from A
where Exists ( select *
from B
where B.c = A.a and B.d = A.b )
테이블 B 를 검색하여, ( a , b ) 동일한 자료가 존재 하는 자료를 검색 하는 문이 됩니다.
장점 ( exists : 1건이 검색 되는 나머지 많은 recode 있다 하여도 1건만 읽고 넘어 갑니다. )
답변 감사합니다.
1. 유효종료일 > to_date('99991231' 에 관해서 : 네 이부분은 pk값이기때문에 항상 존재합니다.
2. offset 절을 이용한건 연계되어오는건이 많을때 일괄처리되면서 lock이 발생되는 현상이 있어서
java에서 전체 처리row갯수를 조회하여 for문으로 일정 row씩 처리되도록 하기위해서 적용해본겁니다.
업무시스템과 같이 돌아가고 있어서 연계처리에 시간이 걸리면 문제가 발생되더라구요.
(예 : 전체 10만건이면 한번에 5000건씩 20번 처리되도록 하며 5000건씩 COMMIT)
원래 쿼리는
UPDATE A
SET 처리결과 = 'S'
WHERE (a,b) IN (SELECT c, d
FROM B
AND 처리결과 IS NULL
ORDER BY c, d
OFFSET 0 ROWS FETCH FIRST 5000 ROWS ONLY)
AND 유효종료일시 > TO_DATE('99991231')
이런 쿼리입니다.
애기해주신부분 살펴보겠습니다. 감사합니다.
ㅇ 으악... 님.. update 처리 하기 위해서라면 더욱더
Exists 부문을 이용하셔야 합니다.
offset 문을 이용한 5000건에 대한 검색 부분은 B table 부분에 대한 것 이고,,
B table 부분은 검색은 최소화 하여야 A table 에서의 update 대상 자료를 선정 하여야 합니다.
결론>
update 대상 목표 테이블 : A table
조건>
1) 유휴종요일시 > to_date('99991231')
2) B table 에 a , b 같은 자료가 존재 하는 것만 처리 한다.
그렇다면,
update A
SET 처리결과 = 'S'
where Exists ( select *
from B
where B.c = A.a and B.d = A.b )
AND 유효종료일시 > TO_DATE('99991231')
이렇게 될것이고,
검정을 위한 Select 문은 1)
select a.* , ( select c || ':' || d from B where B.c = A.a and B.d = A.b and rownum = 1) as B_key
from A
where Exists ( select *
from B
where B.c = A.a and B.d = A.b )
AND 유효종료일시 > TO_DATE('99991231')
그리고 최종 update 처리시.
처리건수가 대량이라면 offset 을 이용한 분할 update 하시면 됩니다.
) 버튼을 클릭하여 작성 하시면 됩니다.