JOIN-064

메뉴별 월 판매 수량에 매장도 메뉴 이름도 안 쓰는데 주문 100만 건을 맞대 본다

중급SELECT버거214 / 700

상황

본사 영업팀은 BI 도구로 「메뉴별 월 판매 수량」을 매일 아침 뽑는다. BI 도구는 사내 표준 뷰 V_ORD_ITEM_DTL만 볼 수 있게 되어 있고, 이 리포트에 나오는 칸은 메뉴 번호, 수량, 줄 수뿐이다. DBA는 "FK가 있으니 주문 조인은 옵티마이저가 알아서 지울 것"이라고 했지만, 실행계획에는 주문과 상품이 그대로 남아 있다. 이 FK는 2014년 신 POS로 옮길 때 검증 시간을 줄이려고 ENABLE NOVALIDATE로 켰다. 아래는 2013년 12월로 조회한 실행계획이다.

테이블과 인덱스

뷰 V_ORD_ITEM_DTL: ORD_ITEM i JOIN ORD o ON o.ORD_NO = i.ORD_NO JOIN ITEM t ON t.ITEM_ID = i.ITEM_ID · i의 컬럼 전부와 o.SHOP_NO, t.ITEM_NM, t.ITEM_TYPE_CD를 내보낸다 · ORD_DT는 i.ORD_DT를 내보낸다
ORD_ITEM: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · 12월 104,750줄(X01 리프 약 390블록, 테이블 약 2,340블록) · ITEM_ID는 NOT NULL
ORD: 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO)(리프 약 2,200블록) / ITEM: 92행 · 5블록 · ITEM_PK (ITEM_ID)
제약: FK_ORD_ITEM_ORD (ORD_ITEM.ORD_NO → ORD) ENABLE NOVALIDATE · ORD_ITEM.ITEM_ID → ITEM FK는 없다
어제 점검에서 주문이 없는 주문상품, 상품 마스터에 없는 메뉴 번호는 모두 0건이었다 · 통계 최신

원본 SQL

sql

SELECT ITEM_ID, SUM(ORD_ITEM_QTY) AS qty, COUNT(*) AS lines
  FROM V_ORD_ITEM_DTL
 WHERE ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY ITEM_ID
 ORDER BY ITEM_ID

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |              |      1 |        |     92 |00:00:00.62 |    4977 |           |
|   1 |  SORT GROUP BY                           |              |      1 |     92 |     92 |00:00:00.62 |    4977 |  6144 (0) |
|*  2 |   HASH JOIN                              |              |      1 |   104K |   104K |00:00:00.58 |    4977 | 1345K (0) |
|   3 |    INDEX FULL SCAN                       | ITEM_PK      |      1 |     92 |     92 |00:00:00.01 |       1 |           |
|*  4 |    HASH JOIN                             |              |      1 |   104K |   104K |00:00:00.55 |    4976 | 6123K (0) |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED  | ORD_ITEM     |      1 |   104K |   104K |00:00:00.04 |    2729 |           |
|*  6 |      INDEX RANGE SCAN                    | ORD_ITEM_X01 |      1 |   104K |   104K |00:00:00.01 |     392 |           |
|   7 |     INDEX FAST FULL SCAN                 | ORD_PK       |      1 |  1000K |  1000K |00:00:00.12 |    2247 |           |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."ITEM_ID"="I"."ITEM_ID")
   4 - access("O"."ORD_NO"="I"."ORD_NO")
   6 - access("I"."ORD_DT">=:V_FROM AND "I"."ORD_DT"<=:V_TO)

문제

FK가 있는데도 4번 줄의 주문 조인이 지워지지 않은 이유와, 2번 줄의 상품 조인이 남은 이유를 각각 설명하시오. 두 조인이 이 SQL에 더하는 일량을 줄 번호와 숫자로 쓰시오.
화면 SQL과 뷰를 고치지 않고 두 조인이 계획에서 사라지게 하는 DDL을 작성하시오. 실행하기 전에 확인할 것과, 운영 중에 실행할 때 주의할 점을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 확인: 둘 다 0 이어야 한다 (0 이 아니면 VALIDATE 가 ORA-02298 로 실패한다)
SELECT COUNT(*) FROM ORD_ITEM i
 WHERE NOT EXISTS (SELECT 1 FROM ORD o WHERE o.ORD_NO = i.ORD_NO);
SELECT COUNT(*) FROM ORD_ITEM i
 WHERE NOT EXISTS (SELECT 1 FROM ITEM t WHERE t.ITEM_ID = i.ITEM_ID);

-- ② 이미 켜져 있는 주문 FK 를 검증한다 (이미 ENABLE 상태라 검증하는 동안 DML 을 막지 않는다)
ALTER TABLE ORD_ITEM MODIFY CONSTRAINT FK_ORD_ITEM_ORD VALIDATE;

-- ③ 상품 FK : 먼저 NOVALIDATE 로 켜서(새 행부터 검사) 짧게 끝내고, 이어서 검증한다
ALTER TABLE ORD_ITEM ADD CONSTRAINT FK_ORD_ITEM_ITEM
  FOREIGN KEY (ITEM_ID) REFERENCES ITEM (ITEM_ID) ENABLE NOVALIDATE;
ALTER TABLE ORD_ITEM MODIFY CONSTRAINT FK_ORD_ITEM_ITEM VALIDATE;

-- ④ 리포트 SQL 과 뷰는 그대로 둔다
SELECT ITEM_ID, SUM(ORD_ITEM_QTY) AS qty, COUNT(*) AS lines
  FROM V_ORD_ITEM_DTL
 WHERE ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY ITEM_ID
 ORDER BY ITEM_ID;

개선 후 실행계획 학습용 예시

xplan

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |              |      1 |        |     92 |00:00:00.07 |    2729 |          |
|   1 |  SORT GROUP BY                         |              |      1 |     92 |     92 |00:00:00.07 |    2729 | 6144 (0) |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED  | ORD_ITEM     |      1 |   104K |   104K |00:00:00.04 |    2729 |          |
|*  3 |    INDEX RANGE SCAN                    | ORD_ITEM_X01 |      1 |   104K |   104K |00:00:00.01 |     392 |          |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("I"."ORD_DT">=:V_FROM AND "I"."ORD_DT"<=:V_TO)

모범 답안 서술

4번 줄은 12월 주문상품 104,750줄을 build로 올리고(6,123K) 주문 PK 100만 항목을 FAST FULL SCAN으로 대어 본다(7번 줄, 2,247블록). 2번 줄은 다시 상품 PK와 맞대어 본다. 결과에는 주문과 상품의 컬럼이 하나도 없는데, 이 두 조인이 Buffers 2,248블록과 0.62초 중 0.5초 남짓을 차지한다. 두 조인은 '짝이 없는 주문상품을 버리는' 일을 하는 셈이다. 옵티마이저가 이런 조인을 지우려면 짝이 '반드시 있다'와 '하나뿐이다'가 둘 다 보장되어야 한다. '하나뿐'은 부모 쪽 PK가 보장한다. '반드시 있다'는 FK가 보장하는데, ENABLE NOVALIDATE는 새로 들어오는 행만 검사했고 이미 있던 행은 검사하지 않았다는 뜻이다. 옵티마이저는 검증되지 않은 제약을 근거로 결과가 달라질 수 있는 변환을 하지 않는다. 그래서 FK가 있는데도 주문 조인이 남는다. 상품 조인에는 FK가 아예 없으니 ITEM_PK만으로는 지울 수 없다. 고치는 방법은 두 조인의 '반드시 있다'를 검증된 제약으로 만드는 것이다. 먼저 NOT EXISTS로 고아 행이 0건인지 확인한다. 한 건이라도 있으면 VALIDATE가 ORA-02298로 실패하므로 원천부터 바로잡는다. 주문 FK는 MODIFY CONSTRAINT ... VALIDATE로 검증하고, 상품 FK는 ENABLE NOVALIDATE로 만든 뒤 검증한다. 이미 켜진 제약을 검증하는 동안에는 테이블에 DML 잠금을 걸지 않으므로 POS 적재를 멈추지 않아도 된다. 다만 247만 행을 부모와 맞대어 보는 시간이 들어서 한가한 시간에 돌린다. 검증이 끝나면 리포트 SQL과 뷰를 그대로 두어도 두 조인이 계획에서 사라진다. Buffers는 4,977 → 2,729, 시간은 0.62 → 0.07초다. 상품 FK의 대가도 적어 둔다. 메뉴를 지우거나 메뉴 번호를 바꾸면 ORD_ITEM에 그 메뉴의 줄이 있는지 확인해야 하는데, ITEM_ID로 시작하는 인덱스가 없어서 주문상품 전체를 읽고, 그동안 주문상품 테이블에 잠금 경합이 생길 수 있다. 메뉴는 지우지 않고 SALE_YN으로 판매 중지를 표시하는 규칙을 함께 정한다.

해설

RELY로 거는 안: RELY DISABLE NOVALIDATE는 검사하지 않고 '믿어도 된다'고만 표시하는 제약이다. 12.2 이후에는 QUERY_REWRITE_INTEGRITY = TRUSTED일 때 조인 제거에도 쓸 수 있다. 검증 시간도 적재 부담도 없지만, 고아가 생기면 옵티마이저가 틀린 결과를 낸다. 고아를 막을 장치가 없는 운영 테이블에는 권하지 않는다.
조인 제거가 일어났는지 확인: 계획에서 테이블 줄이 사라졌는지 보고, +OUTLINE 포맷에서 ELIMINATE_JOIN 줄을 확인한다(교재 08화 §5).
뷰가 o.SHOP_NO를 쓰는 리포트: 매장별 리포트처럼 주문의 컬럼을 쓰면 FK가 있어도 주문 조인은 남는다. 조인 제거는 쓰지 않는 테이블에만 일어난다. 그때는 반정규화된 ORD_ITEM.ORD_DT처럼 필요한 값이 주문상품에 있는지부터 본다(JOIN-033).
NOVALIDATE를 쓴 이유: 이관 때 247만 행 검증을 건너뛰어 작업 시간을 줄인 것은 그때의 판단이다. 검증을 나중에 하기로 했다면 그 일정까지 대장에 남겼어야 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황