JOIN-024

상품 88개의 2년 판매 합계를 내려고 판매상품 110만 줄을 하나씩 상품 마스터에 맞대 본다

기초SELECT유통174 / 700

상황

상품팀의 「상품별 누적 판매」는 2년 치 판매상품을 상품마다 묶어 상품명과 분류, 판매 줄 수, 수량, 금액을 낸다. 상품 관리 화면 첫 페이지에 붙어 있어 하루 3천 번쯤 호출된다. 실행계획은 해시 조인이고 Buffers도 두 테이블을 한 번씩 읽은 만큼이다. 그런데 AWR의 CPU 상위에 이 SQL이 올라 있다. 오과장은 "블록을 더 줄일 데가 없으면 그대로 둬야 하나"라고 묻는다.

테이블과 인덱스

CF01 상품: 109행 · 5블록 · CF01_PK (ITM_NO) · 판매상품 88 · 추가상품 1 · 원재료 20
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 판매 기록이 있는 상품은 88개
통계 최신 · 아래 CPU는 같은 SQL을 10번 돌려 잰 CPU used by this session의 한 번 평균

원본 SQL

sql

SELECT p.ITM_NO, p.ITM_NM, p.ITM_CL_CD,
       COUNT(*) AS sle_lines, SUM(i.SLE_QTY) AS sle_qty,
       SUM(i.SLE_QTY * i.SLE_UPRC) AS sle_amt
  FROM CF01 p, CF11 i
 WHERE i.ITM_NO = p.ITM_NO
 GROUP BY p.ITM_NO, p.ITM_NM, p.ITM_CL_CD

실행계획 학습용 예시 · CPU 비율은 교재 18화 Q2 rev02 실측을 참고

xplan

-------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     88 |00:00:00.41 |    8209 |          |
|   1 |  HASH GROUP BY      |      |      1 |    109 |     88 |00:00:00.41 |    8209 | 1269K (0)|
|*  2 |   HASH JOIN         |      |      1 |   1096K|   1096K|00:00:00.29 |    8209 | 1321K (0)|
|   3 |    TABLE ACCESS FULL| CF01 |      1 |    109 |    109 |00:00:00.01 |       5 |          |
|   4 |    TABLE ACCESS FULL| CF11 |      1 |   1096K|   1096K|00:00:00.06 |    8204 |          |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."ITM_NO"="P"."ITM_NO")

[한 번 실행]  CPU used by this session  39 cs

문제

2번 줄의 A-Rows로 이 SQL이 해시 조인에서 몇 번 짝을 맞춰 보는지 설명하고, Buffers가 작은데 CPU가 드는 이유를 서술하시오.
결과가 바뀌지 않게 SQL을 고쳐 조인에 대는 행을 줄이시오. 고친 SQL의 결과가 원본과 같은 근거도 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- 묶는 기준(ITM_NO)이 조인 키와 같다: CF11 만으로 먼저 88줄로 묶고, 그 88줄에 상품 마스터를 붙인다
SELECT p.ITM_NO, p.ITM_NM, p.ITM_CL_CD,
       i.sle_lines, i.sle_qty, i.sle_amt
  FROM CF01 p,
       (SELECT ITM_NO,
               COUNT(*) AS sle_lines, SUM(SLE_QTY) AS sle_qty,
               SUM(SLE_QTY * SLE_UPRC) AS sle_amt
          FROM CF11
         GROUP BY ITM_NO) i
 WHERE i.ITM_NO = p.ITM_NO

개선 후 실행계획 학습용 예시 · CPU 비율은 교재 18화 Q2 rev02 실측을 참고

xplan

--------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |     88 |00:00:00.19 |    8209 |          |
|*  1 |  HASH JOIN           |      |      1 |     88 |     88 |00:00:00.19 |    8209 | 1321K (0)|
|   2 |   TABLE ACCESS FULL  | CF01 |      1 |    109 |    109 |00:00:00.01 |       5 |          |
|   3 |   VIEW               |      |      1 |     88 |     88 |00:00:00.19 |    8204 |          |
|   4 |    HASH GROUP BY     |      |      1 |     88 |     88 |00:00:00.19 |    8204 | 1153K (0)|
|   5 |     TABLE ACCESS FULL| CF11 |      1 |   1096K|   1096K|00:00:00.06 |    8204 |          |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("I"."ITM_NO"="P"."ITM_NO")

[한 번 실행]  CPU used by this session  13 cs

모범 답안 서술

2번 줄 HASH JOIN의 A-Rows가 1,096K다. CF01 109행으로 해시 테이블을 만든 뒤, 판매상품 110만 줄을 한 줄씩 그 해시 테이블에 맞대어 짝을 찾았다는 뜻이다. 그렇게 만든 110만 줄을 1번 줄 HASH GROUP BY가 다시 88줄로 묶는다. 두 테이블을 한 번씩 읽었으므로 Buffers는 8,209로 작지만, 110만 번의 해시 탐색과 110만 줄의 조인 결과를 만드는 일이 CPU를 쓴다. 하루 3천 번이면 CPU만 약 20분이다. 묶는 기준(ITM_NO)이 조인 키와 같으므로, 조인하기 전에 CF11만으로 먼저 묶을 수 있다. 인라인 뷰에서 CF11을 ITM_NO로 묶어 88줄로 만든 뒤 CF01을 붙인다. 해시 조인에 대는 행이 110만 줄에서 88줄로 줄어든다. 결과가 같은 근거는 CF01이 ITM_NO마다 한 행(PK)이라는 점이다. 원본에서도 판매상품 한 줄은 상품 한 행과만 짝지어지므로, 상품별 합계를 먼저 내고 붙여도 줄이 불지 않고 금액도 그대로다. 판매가 없는 원재료 20개와 추가상품이 빠지는 것도 두 SQL이 같다(내부 조인). Buffers는 8,209로 같다. 대신 CPU가 39 → 13cs로 약 3분의 1이 되고, A-Time은 0.41 → 0.19초로 준다.

해설

블록이 같은 개선: Buffers만 보면 개선이 없다. 18화 Q2 rev02도 Buffers는 같고 CPU가 3.3 ~ 3.8배 줄었다. 그래서 CPU를 같은 조건으로 재서 함께 적어야 개선의 근거가 된다 (교재 18화 §6).
옵티마이저도 할 줄 안다: 오라클은 이 변환(Group-by Placement)을 비용을 보고 스스로 하기도 한다. 그때 계획에는 VIEW VW_GBC_...가 보인다. 이 SQL에서는 하지 않았으므로 사람이 썼다. 스칼라 서브쿼리 같은 모양 하나가 변환을 막는 일도 있다 (교재 18화 §6.3).
1 쪽이 하나가 아니면: 붙일 쪽이 조인 키마다 여러 행이면(예: 상품 가격 이력) 먼저 묶은 합계가 그 행 수만큼 곱해진다. 먼저 묶을 때는 붙일 쪽이 키마다 한 행인지부터 확인한다 (교재 08화).
상품명을 GROUP BY에 넣지 않으므로 묶는 키도 짧아진다. 같은 이름의 상품이 둘 있어도 합쳐지지 않는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · CPU 비율은 교재 18화 Q2 rev02 실측을 참고 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · CPU 비율은 교재 18화 Q2 rev02 실측을 참고 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황