문제출처: SQLP 자격검정 핵심노트 2, 고급 SQL 튜닝 I 39번 문제
국가공인 SQLP 자격검정 핵심노트 2
SQLP 자격이 요구하는 핵심 기술인 SQL 고급 활용 및 튜닝에 초점을 맞춰 총 462문제를 수록하였다. 단원별 문제는 순서대로 풀면서 해설을 읽는 과정에 자연스럽게 중요한 핵심 내용을 파악할 수
www.aladin.co.kr
문제: 아래 프로그램을 튜닝하시오.
[인덱스 구성]
고객_PK: 고객번호
고객_X1: 회원사코드
계좌_PK: 계좌번호
계좌_X1: 고객번호
일별예수금잔고_PK: 계좌번호 + 기준일자
DECLARE
V_CNT NUMBER DEFAULT 0;
V_BASE_DT VARCHAR2(8);
BEGIN
FOR C IN (
SELECT B.계좌번호
FROM 고객 A, 계좌 B
WHERE A.회원사코드 LIKE '8%'
AND B.고객번호 = A.고객번호
)
LOOP
V_BASE_DT := GET_BASE_DT (C.계좌번호);
DELETE FROM 일별예수금잔고
WHERE 기준일자 = V_BASE_DT
AND 계좌번호 = C.계좌번호;
V_CNT := V_CNT + SQL%ROWCOUNT;
COMMIT;
END LOOP;
DBMS_OUTPUT.PUT_LINE (V_CNT || '건이 삭제되었습니다.');
END;
쿼리분석
1. 고객의 회원사코드 조건에 맞는 계좌번호 정보를 출력한다.
2. 그 계좌번호에 GET_BASE_DT 함수를 적용하여 계좌번호별 기준일자를 출력한 뒤, V_BASE_DT 변수에 넣는다.
3. 일별예수금잔고 테이블에서 기준일자가 V_BASE_DT인 데이터를 삭제한다.
4. V_CNT 변수에 삭제된 행의 개수를 찾는다.
5. 삭제된 행의 개수를 출력한다.
문제점과 개선점
1. LOOP문을 이용해 행 하나하나 처리하려고 하고 있으며, 또 LOOP 안에 COMMIT이 있다. 매번 COMMIT을 함으로써 부하가 클 것이다. -> COMMIT은 마지막에 한 번 하는 것으로 바꾸며, 또 LOOP를 제거한다.
2. 고객과 계좌 테이블의 조인 테이블과, 일별예수금잔고 테이블을 조회하기 위해 쿼리를 두 번 날려야 한다. 업무처리에 비효율적이므로, 효율화를 위해 가급적 ONE-SQL로 처리하도록 한다.
모범답안
DECLARE
V_CNT NUMBER DEFAULT 0;
BEGIN
DELETE FROM 일별예수금잔고
WHERE (계좌번호, 기준일자) IN
(SELECT B.계좌번호, GET_BASE_DT(B.계좌번호)
FROM 고객 A, 계좌 B
WHERE A.회원사코드 LIKE '8%'
AND B.고객번호 = A.고객번호);
V_CNT := SQL%ROWCOUNT;
COMMIT;
DBMS__OUTPUT.PUT_LINE (V_CNT || '건이 삭제되었습니다.');
END;
