JOIN-062

커버링 인덱스로 8블록이면 끝나는 「누적 판매 수량」이 상품 상세 화면에서는 1천 블록을 읽는다

기초SELECT한빛유통212 / 700

상황

상품 상세 화면 상단의 「누적 판매 수량」은 상품 하나가 지금까지 몇 줄, 몇 개 팔렸는지를 보여 준다. 상품 상세가 열릴 때마다 돌아서 하루 3만 번 호출된다. 지난달 오과장이 이 칸을 위해 IX_T_ORD_DET_ITM (ITM_ID, ORD_QTY)를 만들었다. SQL Developer에서 T_ORD_DET를 직접 조회하면 8블록이었는데, 화면에서는 1,029블록을 읽는다. 화면 코드는 사내 표준대로 주문 공통 뷰 V_ORD_DET를 쓴다.

테이블과 인덱스

뷰 V_ORD_DET: T_ORD_DET d JOIN T_ORD o ON o.ORD_SEQ = d.ORD_SEQ · d의 컬럼 전부와 o.CUS_ID, o.ORD_DT, o.ORD_ST를 내보낸다 · 주문 관련 화면 30여 개가 함께 쓴다
T_ORD_DET: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · IX_T_ORD_DET_ITM (ITM_ID, ORD_QTY) · 상품마다 2,000줄(테이블 전체에 흩어져 있다)
T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ)(리프 약 220블록)
신 시스템에는 FK가 하나도 없다(교재 01화) · 지금 데이터에서 주문 헤더가 없는 주문상세는 0건 · 통계 최신

원본 SQL

sql

SELECT COUNT(*) AS det_cnt, SUM(ORD_QTY) AS sale_qty
  FROM V_ORD_DET
 WHERE ITM_ID = :v_itm

실행계획 학습용 예시

xplan

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                  |      1 |        |      1 |00:00:00.02 |    1029 |           |
|   1 |  SORT AGGREGATE                         |                  |      1 |      1 |      1 |00:00:00.02 |    1029 |           |
|*  2 |   HASH JOIN                             |                  |      1 |   2000 |   2000 |00:00:00.02 |    1029 | 1599K (0) |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED  | T_ORD_DET        |      1 |   2000 |   2000 |00:00:00.01 |     806 |           |
|*  4 |     INDEX RANGE SCAN                    | IX_T_ORD_DET_ITM |      1 |   2000 |   2000 |00:00:00.01 |       8 |           |
|   5 |    INDEX FAST FULL SCAN                 | PK_T_ORD         |      1 |   100K |   100K |00:00:00.01 |     223 |           |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."ORD_SEQ"="D"."ORD_SEQ")
   4 - access("D"."ITM_ID"=:V_ITM)

문제

결과에 T_ORD의 컬럼을 하나도 쓰지 않는데 5번 줄에 PK_T_ORD가 나오는 이유와, 커버링 인덱스를 만들었는데도 3번 줄에서 테이블을 읽는 이유를 설명하시오.
이 화면이 인덱스만 읽고 끝나도록 고치시오. 뷰를 쓰는 다른 화면에도 효과가 있는 방법을 쓰고, 그 방법을 적용하기 전에 확인할 것을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 고아 확인 (0 이어야 한다)
SELECT COUNT(*)
  FROM T_ORD_DET d
 WHERE NOT EXISTS (SELECT 1 FROM T_ORD o WHERE o.ORD_SEQ = d.ORD_SEQ);

-- ② 주문상세 → 주문 FK : 부모 쪽에 PK_T_ORD 가 있고, 자식 쪽 ORD_SEQ 는 PK 컬럼이라 NOT NULL 이다
ALTER TABLE T_ORD_DET ADD CONSTRAINT FK_T_ORD_DET_ORD
  FOREIGN KEY (ORD_SEQ) REFERENCES T_ORD (ORD_SEQ);

-- ③ 화면 SQL 은 그대로 둔다 : 옵티마이저가 뷰 안의 T_ORD 조인을 지운다(조인 제거)
SELECT COUNT(*) AS det_cnt, SUM(ORD_QTY) AS sale_qty
  FROM V_ORD_DET
 WHERE ITM_ID = :v_itm;

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

xplan

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                  |      1 |        |      1 |00:00:00.01 |       8 |
|   1 |  SORT AGGREGATE     |                  |      1 |      1 |      1 |00:00:00.01 |       8 |
|*  2 |   INDEX RANGE SCAN  | IX_T_ORD_DET_ITM |      1 |   2000 |   2000 |00:00:00.01 |       8 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("D"."ITM_ID"=:V_ITM)

모범 답안 서술

뷰 V_ORD_DET 안에는 T_ORD와의 내부 조인이 들어 있다. 화면 SQL이 T_ORD의 컬럼을 하나도 쓰지 않아도, 내부 조인은 짝이 없는 주문상세를 버리는 일을 한다. 옵티마이저는 모든 주문상세에 주문 헤더가 반드시 있다는 보장이 없으면 이 조인을 지울 수 없다. 그 보장이 FK다. 한빛유통 신 시스템에는 FK가 없으므로, 2번 줄에서 상품 하나의 2,000줄을 주문 10만 건과 맞대어 본다(5번 줄 PK_T_ORD FAST FULL SCAN 223블록). 조인이 남아 있으니 조인 키 ORD_SEQ도 필요하다. 그런데 IX_T_ORD_DET_ITM (ITM_ID, ORD_QTY)에는 ORD_SEQ가 없다. 그래서 3번 줄이 2,000개 ROWID로 테이블을 찾아가 798블록을 더 읽었다. 커버링 인덱스는 뷰 안의 조인 때문에 깨졌다. 고치는 방법은 FK를 거는 것이다. 먼저 NOT EXISTS로 부모 없는 주문상세가 0건인지 확인하고, T_ORD_DET (ORD_SEQ) → T_ORD에 FK를 건다. 부모 쪽에는 PK가 있고 자식 쪽 ORD_SEQ는 PK 컬럼이라 NOT NULL이다. 이제 옵티마이저는 이 조인이 행 수를 바꾸지 않는다는 것을 알고 조인을 지운다(조인 제거). 화면 SQL은 고치지 않아도 인덱스 범위 스캔 하나로 끝난다. Buffers는 1,029 → 8이고, 하루 3만 번이면 약 3,087만 → 24만 블록이다. 뷰를 쓰면서 T_ORD의 컬럼을 쓰지 않는 다른 화면도 함께 좋아진다. 대가도 있다. 주문상세를 넣을 때마다 부모 주문이 있는지 PK로 확인하고, 주문을 지울 때는 주문상세를 확인한다. 주문상세의 FK 컬럼은 PK의 선두라서 인덱스가 있으므로, 주문을 지울 때 주문상세 전체를 읽거나 잠그는 일은 없다. FK를 거는 순간 20만 행을 한 번 검사하는 시간도 든다.

해설

다른 정답: 이 화면만 FROM T_ORD_DET로 직접 읽어도 8블록이다. 하지만 뷰를 쓰는 다른 화면은 그대로이고, "화면은 공통 뷰를 쓴다"는 사내 표준과도 어긋난다. FK는 데이터의 규칙을 DB가 지키게 하면서 뷰를 쓰는 모든 SQL에 효과가 있다.
인덱스에 ORD_SEQ를 더하면: (ITM_ID, ORD_QTY, ORD_SEQ)로 바꾸면 테이블은 안 읽지만 조인은 남아 PK_T_ORD 223블록과 해시 조인이 그대로다(약 230블록).
운영 중에 건다면: ENABLE NOVALIDATE로 먼저 켠 뒤 VALIDATE하면 검증하는 동안 DML을 막지 않는다. 다만 조인 제거는 검증까지 끝나야 일어난다.
지울 수 있는 조건: 조인을 지우려면 '상대 쪽에 반드시 있다(FK)'와 '딱 하나만 있다(PK)'가 둘 다 증명되어야 한다. 행 수가 바뀌지 않는다는 증명이다(교재 08화 조인 제거 표).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황