카테고리를 고르면 팔린 상품 일곱 개를 보여 주는 팝업이 판매상품 인덱스를 통째로 읽는다

기초SELECT유통256 / 700

상황

본사 MD의 「행사 상품 고르기」 팝업은 카테고리를 고르면 그 카테고리 상품 가운데 한 번이라도 팔린 상품만 보여 준다. MD 열 명이 행사를 짤 때마다 카테고리를 바꿔 가며 눌러 하루 2천 번쯤 호출되고, 한 번에 0.05초 남짓 걸린다. DBA는 "0.05초짜리를 왜 보느냐"라고 하지만, 하루 합계로는 이 팝업이 판매상품 인덱스를 가장 많이 읽는다.

테이블과 인덱스

CF01 상품: 109행 · 5블록 · CF01_PK (ITM_NO) · 카테고리 311(텀블러)에 상품 9개, 그중 판매 이력이 있는 상품 7개
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) 리프 약 4,300블록 · ITM_NO로 시작하는 인덱스는 없다
팔린 상품은 상품마다 판매 줄이 수천 ~ 수만 줄이다 · 통계 최신

원본 SQL

sql

SELECT p.ITM_NO, p.ITM_NM
  FROM CF01 p
 WHERE p.CTG_NO = :ctg
   AND EXISTS (SELECT 1
                 FROM CF11 b
                WHERE b.ITM_NO = p.ITM_NO)
 ORDER BY p.ITM_NO

실행계획 학습용 예시

xplan

--------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |      1 |        |      7 |00:00:00.05 |    4338 |
|   1 |  SORT ORDER BY         |         |      1 |      8 |      7 |00:00:00.05 |    4338 |
|*  2 |   HASH JOIN SEMI       |         |      1 |      8 |      7 |00:00:00.05 |    4338 |
|*  3 |    TABLE ACCESS FULL   | CF01    |      1 |      8 |      9 |00:00:00.01 |       5 |
|   4 |    INDEX FAST FULL SCAN| CF11_PK |      1 |   1096K|   1096K|00:00:00.04 |    4333 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."ITM_NO"="P"."ITM_NO")
   3 - filter("P"."CTG_NO"=:CTG)

문제

4번 줄이 무엇을 읽었는지, 상품 9개의 판매 여부를 가리는 데 왜 그만큼이 필요했는지 설명하시오.
상품마다 판매가 '있는지'만 확인하고 끝나도록 인덱스를 설계하고 SQL을 작성하시오. 하루 2천 번 호출로 따지면 얼마나 줄어드는지와 인덱스를 더하는 대가도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 상품번호로 바로 찾아 들어갈 인덱스 (세미 조인은 첫 항목만 확인하면 된다)
CREATE INDEX CF11_X01 ON CF11 (ITM_NO);

SELECT p.ITM_NO, p.ITM_NM
  FROM CF01 p
 WHERE p.CTG_NO = :ctg
   AND EXISTS (SELECT 1
                 FROM CF11 b
                WHERE b.ITM_NO = p.ITM_NO)
 ORDER BY p.ITM_NO;
-- 옵티마이저가 해시 세미 조인을 고집하면 서브쿼리에 /*+ UNNEST NL_SJ */

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

xplan

---------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |      7 |00:00:00.01 |      32 |
|   1 |  SORT ORDER BY        |          |      1 |      8 |      7 |00:00:00.01 |      32 |
|   2 |   NESTED LOOPS SEMI   |          |      1 |      8 |      7 |00:00:00.01 |      32 |
|*  3 |    TABLE ACCESS FULL  | CF01     |      1 |      8 |      9 |00:00:00.01 |       5 |
|*  4 |    INDEX RANGE SCAN   | CF11_X01 |      9 |  12463 |      7 |00:00:00.01 |      27 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("P"."CTG_NO"=:CTG)
   4 - access("B"."ITM_NO"="P"."ITM_NO")

모범 답안 서술

4번 줄은 CF11_PK 인덱스 전체를 FAST FULL SCAN으로 읽었다(4,333블록, 110만 항목). 이 인덱스는 (SLE_NO, ITM_NO) 순서라서 ITM_NO가 선두가 아니다. 상품번호로 바로 찾아 들어갈 수 없으니, 옵티마이저는 테이블보다 작은 인덱스를 통째로 읽어 해시 세미 조인을 했다. 상품 9개의 판매 여부를 가리려고 110만 줄을 다 대어 본 것이다. 세미 조인은 짝이 있는지만 본다. 판매 줄이 1만 줄 있어도 첫 건을 찾으면 그 상품은 '있다'로 끝나고 다음 상품으로 넘어간다. CF11 (ITM_NO) 인덱스를 만들면 옵티마이저는 NL 세미 조인(NESTED LOOPS SEMI)을 고른다. 상품 9개마다 인덱스를 한 번(3블록) 찾아 첫 항목만 확인하므로 4번 줄의 E-Rows는 12,463인데 A-Rows는 7이다. 팔린 적 없는 2개는 찾아 들어가 아무것도 없음을 확인하고 끝난다. Buffers는 4,338 → 32로 준다. 하루 2천 번이면 약 870만 블록이 6만 4천 블록이 된다. 대가도 있다. CF11은 가장 큰 테이블이라 인덱스가 하나 늘면 판매가 들어올 때마다 인덱스도 함께 갱신해야 한다. 판매 적재 시간을 재 보고 승인받는다.

해설

E-Rows 12,463, A-Rows 7: 4번 줄의 E-Rows는 상품 하나에 매달린 판매 줄 수의 추정이다. 세미 조인은 그중 첫 줄만 확인하므로 A-Rows는 판매된 상품 수다. 조인으로 바꾸면 이 7이 수만 줄이 된다.
(ITM_NO, SLE_NO)도 답이다: 상품별 기간 판매를 보는 다른 화면이 있다면 기간 컬럼까지 붙인 인덱스 하나로 두 화면을 함께 받는다. 세미 조인만 보면 ITM_NO 하나로 충분하다.
인덱스 없이: 판매 이력이 있는지를 상품 마스터에 따로 적어 두는 방법도 있지만, 판매 적재 배치가 그 값을 늘 맞게 고쳐야 한다. 값이 어긋나면 팝업이 조용히 틀린다.
적재 비용 재기: 새 인덱스는 판매가 들어올 때마다 상품별 리프에 흩어져 들어간다. INVISIBLE로 만들어 적재 시간을 재 본 뒤 승인서에 적는다(교재 04화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황