튜닝 중 재미있는 사례가 있었다, 사용자 요청으로 인한 데이터 일괄 수정 중
재고부서, 물품코드, 입출 월별로 적요가 모두 공란이면 마지막 입출일자중 마지막 입출번호 데이터에만
적요를 "특이사항 없음" 이라고 적어주세요
아래는 테스트 데이터 예시를 들어보았다, 빨간 표기가 사용자 원하는 일괄수정이다
| 재고부서 | 물품코드 | 입출일자 | 입출번호 | 적요 |
| 구매팀 | A | 20241001 | 0001 | |
| 구매팀 | A | 20241001 | 0002 | |
| 구매팀 | A | 20241002 | 0001 | |
| 구매팀 | A | 20241015 | 0001 | |
| 구매팀 | A | 20241015 | 0002 | 특이사항 없음 |
| 구매팀 | A | 20241105 | 0001 | |
| 구매팀 | A | 20241107 | 0001 | |
| 구매팀 | A | 20241107 | 0002 | |
| 구매팀 | A | 20241107 | 0003 | |
| 구매팀 | A | 20241107 | 0004 | |
| 구매팀 | A | 20241125 | 0001 | 추가입고 |
| 구매팀 | A | 20241201 | 0001 | |
| 구매팀 | A | 20241202 | 0001 | 입고지연 |
| 구매팀 | A | 20241203 | 0001 | |
| 구매팀 | B | 20241002 | 0001 | |
| 구매팀 | B | 20241002 | 0002 | |
| 구매팀 | B | 20241002 | 0003 | |
| 구매팀 | B | 20241013 | 0001 | |
| 구매팀 | B | 20241021 | 0001 | 특이사항 없음 |
- 기존 쿼리
좀 복잡해보이지만 간단하다 타겟은 입출년도가 23/24년, exitst절은 품목마스터에 등록된 물폼정보만
그룹 기준은 기관-부서-물품-입출월 이고 모두 공란일때 는 HAVING조건을 활용하여 구현하였다.
문제는 마지막 일에서 마지막 순번을 구하기 위해 2차 GROUPING을 하며 이때 셀프조인을 동반하여
불필요하게 동일 데이터 엑세스를 두번했다는것이다..
MERGE INTO 입출고내역 IO
USING(
SELECT B.기관코드,B.재고부서,B.물품코드,B.입출일자, MAX(B.입출번호) 입출번호
FROM
(SELECT 기관코드,재고부서,물품코드,SUBSTR(입출일자,1,6) IOMM, MAX(입출일자) 입출일자, MAX(적요)
FROM 입출고내역 A
WHERE 기관코드 = '60'
AND 입출일자 BETWEEN '20230101' AND '20241231'
AND EXISTS(SELECT D.물품코드
FROM 품목마스터 D
WHERE D.물품코드 = A.물품코드
AND D.물품구분 = A.물품구분
AND A.입출일자 BETWEEN D.시작일자 AND D.종료일자
AND D.관리여부 = 'Y')
GROUP BY 기관코드,재고부서,물품코드,SUBSTR(입출일자,1,6)
HAVING MAX(적요) IS NULL
) A INNER JOIN 입출고내역 B
ON B.기관코드 = A.기관코드
AND B.재고부서 = A.재고부서
AND B.물품코드 = A.물품코드
AND B.입출일자 = A.입출일자
GROUP BY B.기관코드,B.재고부서,B.물품코드,B.입출일자
) TARGET
ON (TARGET.기관코드 = IO.기관코드
AND TARGET.재고부서 = IO.재고부서
AND TARGET.물품코드 = IO.물품코드
AND TARGET.입출일자 = IO.입출일자
AND TARGET.입출번호 = IO.입출번호)
WHEN MATCHED THEN
UPDATE SET IO.적요 = '특이사항 없음';
- 실행계획
풀스캔이 일어난 이유는 표본이 적어서.. 풀스캔으로 되었고 중요한건 입출고내역 테이블에 3번 접근했다는것
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 75 | 450 | 224 (1)| 00:00:01 |
| 1 | MERGE | 입출고내역 | | | | |
| 2 | VIEW | | | | | |
| 3 | NESTED LOOPS | | 75 | 12750 | 224 (1)| 00:00:01 |
| 4 | NESTED LOOPS | | 75 | 12750 | 224 (1)| 00:00:01 |
| 5 | VIEW | | 75 | 2100 | 209 (1)| 00:00:01 |
| 6 | SORT GROUP BY | | 75 | 5250 | 209 (1)| 00:00:01 |
| 7 | NESTED LOOPS | | 75 | 5250 | 208 (1)| 00:00:01 |
| 8 | NESTED LOOPS | | 75 | 5250 | 208 (1)| 00:00:01 |
| 9 | VIEW | | 75 | 2325 | 178 (1)| 00:00:01 |
|* 10 | FILTER | | | | | |
| 11 | SORT GROUP BY | | 75 | 5250 | 178 (1)| 00:00:01 |
|* 12 | HASH JOIN RIGHT SEMI | | 1497 | 102K| 177 (0)| 00:00:01 |
|* 13 | TABLE ACCESS STORAGE FULL| 품목마스터 | 74 | 2368 | 7 (0)| 00:00:01 |
|* 14 | TABLE ACCESS STORAGE FULL| 입출고내역 | 9430 | 349K| 170 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | PK_입출고내역 | 1 | | 1 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID | 입출고내역 | 1 | 39 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_입출고내역 | 1 | | 1 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID | 입출고내역 | 1 | 142 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------ 튜닝쿼리
일자는 YYYYMMDD / 번호는 NNNN 으로 자릿수가 포매팅된 VARCHAR라는 타입을 활용하면
(RAW데이터가 VARCHAR가 아니여도 포맷팅하면 된다)
|| 으로 합쳐서 MAX로 추출시 한번에 최종일자 중 최종순번이 구해진다! 2차 그루핑을 스킵~
MERGE INTO 입출고내역 IO
USING(
SELECT A.기관코드, A.재고부서, A.물품코드
, SUBSTR(A.최종입출,1,8) 최종입출일자
, SUBSTR(A.최종입출,9,4) 최종입출번호
FROM (SELECT A.기관코드,A.재고부서,A.물품코드
,SUBSTR(A.입출일자,1,6) IOMM
, MAX(A.입출일자||A.입출번호) 최종입출
, MAX(A.적요) 적요
FROM 입출고내역 A
WHERE A.기관코드 = '60'
AND A.입출일자 BETWEEN '20230101' AND '20241231'
AND EXISTS(SELECT D.물품코드
FROM 품목마스터 D
WHERE D.물품코드 = A.물품코드
AND D.물품구분 = A.물품구분
AND A.입출일자 BETWEEN D.FROMDD AND D.TODD
AND D.관리여부 = 'Y'
)
GROUP BY A.기관코드,A.재고부서,A.물품코드,SUBSTR(입출일자,1,6)
HAVING MAX(A.적요) IS NULL) A
) TARGET
ON (TARGET.기관코드 = IO.기관코드
AND TARGET.재고부서 = IO.재고부서
AND TARGET.물품코드 = IO.물품코드
AND TARGET.최종입출일자 = IO.입출일자
AND TARGET.최종입출번호 = IO.입출번호)
WHEN MATCHED THEN
UPDATE SET IO.적요 = '특이사항 없음';
- 실행계획
원하는대로 셀프조인 스킵하며 데이터 표본이 많을수록 큰차이가 벌어질것이다.
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 6 | 193 (1)| 00:00:01 |
| 1 | MERGE | 입출고내역 | | | | |
| 2 | VIEW | | | | | |
| 3 | NESTED LOOPS | | 1 | 168 | 193 (1)| 00:00:01 |
| 4 | NESTED LOOPS | | 75 | 168 | 193 (1)| 00:00:01 |
| 5 | VIEW | | 75 | 1950 | 178 (1)| 00:00:01 |
|* 6 | FILTER | | | | | |
| 7 | SORT GROUP BY | | 75 | 5625 | 178 (1)| 00:00:01 |
|* 8 | HASH JOIN RIGHT SEMI | | 1497 | 109K| 177 (0)| 00:00:01 |
|* 9 | TABLE ACCESS STORAGE FULL| 품목마스터 | 74 | 2368 | 7 (0)| 00:00:01 |
|* 10 | TABLE ACCESS STORAGE FULL| 입출고내역 | 9430 | 395K| 170 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_입출고내역 | 1 | | 1 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | 입출고내역 | 1 | 142 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------- STAT
논리적 블럭엑세스(cons gets)도 효과적이고 그루핑 횟수도 줄다보니 memort sort도 줄었다.
| AS IS | TO BE | |
| recursive calls | 54 | 7 |
| db block gets | 104 | 106 |
| consistent gets | 1428 | 847 |
| physical reads | 10 | 1 |
| redo size | 31688 | 30568 |
| bytes sent via SQL*Net to client | 116 | 116 |
| bytes received via SQL*Net from client | 1336 | 1200 |
| SQL*Net roundtrips to/from client | 1 | 1 |
| sorts (memory) | 29 | 1 |
| sorts (disk) | 0 | 0 |
| rows processed | 0 | 0 |
튜닝의 기본인 한번 읽은 데이터는 두번 엑세스 하지 않는다를 지키기 위해 튜닝한 간단하지만 재미있는 사례였다.
'Tuning' 카테고리의 다른 글
| 생각보다 자주 놓치는 간단한 집계 쿼리 튜닝 요소 (0) | 2025.06.09 |
|---|---|
| Oracle 실행 계획 (1) | 2025.06.04 |
| 동일 패턴의 스칼라 서브쿼리 형태 인라인 뷰로 통합으로 성능 향상 사례 (0) | 2025.05.28 |