「버거류 일 판매」가 인덱스를 1,485블록 읽는데 자이언트더블버거가 빠져 있다

실전SELECT버거94 / 700

상황

버거원 상품기획팀의 「버거류 일 판매」는 날짜 하나를 고르면 버거 메뉴별 판매 수량을 보여 준다. 하루 수백 번 연다. 2년 전 개발자는 버거 메뉴의 ITEM_ID가 1 ~ 17이라 BETWEEN 1 AND 17로 썼다. 상품기획팀이 "자이언트더블버거가 목록에 없다"고 문의했고, 같은 시기에 이 화면이 인덱스를 1,485블록 읽는다는 점검 결과도 나왔다. 자이언트더블버거는 나중에 추가된 메뉴로 ITEM_ID가 84다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ITEM_ID 1 ~ 92에 고르게 분포(상품당 약 26,900행, 하루 약 37행) · ORD_DT VARCHAR2(8) 2012 ~ 2013 · 2013-11-15 하루 약 3,430행
ITEM 상품: 92행 · 5블록 · ITEM_PK (ITEM_ID) · ITEM_TYPE_CD 100100 버거 18종(1 ~ 17과 84) · 100101 세트 · 100102 치킨 · 100103 디저트 · 100104 아이스크림 · 100105 음료
번호 대역과 분류가 맞지 않는 메뉴가 이미 있다(예: 74 치즈스틱은 디저트인데 음료 번호 사이에 있다)
인덱스 ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · ORD_ITEM_X02 (ITEM_ID, ORD_DT, ORD_ITEM_QTY): BLEVEL 2 · 리프 약 8,500블록(리프 한 블록에 약 290항목) · 「상품 월간 추이」가 쓴다

원본 SQL

sql

SELECT ITEM_ID, SUM(ORD_ITEM_QTY) AS qty
  FROM ORD_ITEM
 WHERE ITEM_ID BETWEEN 1 AND 17
   AND ORD_DT = :v_dt
 GROUP BY ITEM_ID
 ORDER BY ITEM_ID

실행계획 학습용 예시 · 규모는 schemas 그대로 · 상품 분류는 적재 스크립트(2.sql) 그대로

xplan

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |      1 |        |     17 |00:00:00.03 |    1485 |
|   1 |  SORT GROUP BY NOSORT|              |      1 |     17 |     17 |00:00:00.03 |    1485 |
|*  2 |   INDEX RANGE SCAN   | ORD_ITEM_X02 |      1 |    667 |    631 |00:00:00.03 |    1485 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ITEM_ID">=1 AND "ORD_DT"=:V_DT AND "ITEM_ID"<=17)
       filter("ORD_DT"=:V_DT)

문제

2번 줄이 1,485블록을 읽는 이유를 access와 filter로 설명하시오. 결과에서 자이언트더블버거가 빠진 이유도 설명하시오.
세 가지 고침을 비교하시오. ① ITEM_ID IN (1, 2, …, 17, 84) ② ITEM에서 버거 분류(ITEM_TYPE_CD = '100100')를 읽어 조인 ③ 새 인덱스 (ORD_DT, ITEM_ID, ORD_ITEM_QTY). 각각 읽는 블록 수, 결과가 맞는지, 메뉴가 늘거나 바뀔 때 생기는 일, 적재 비용을 쓰시오.
하나를 골라 SQL(필요하면 DDL)을 작성하고, 고른 방식의 실행계획에서 ORD_ITEM_X02가 몇 번, 어떤 조건으로 읽히는지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT i.ITEM_ID, i.ITEM_NM, SUM(oi.ORD_ITEM_QTY) AS qty
  FROM ITEM i, ORD_ITEM oi
 WHERE i.ITEM_TYPE_CD = '100100'          -- 번호 대역이 아니라 분류로 고른다 (84 를 포함한 18 종)
   AND oi.ITEM_ID     = i.ITEM_ID         -- 메뉴마다 (ITEM_ID = , ORD_DT = ) 로 X02 를 찾는다
   AND oi.ORD_DT      = :v_dt
 GROUP BY i.ITEM_ID, i.ITEM_NM
 ORDER BY i.ITEM_ID;

개선 후 실행계획 학습용 예시 · 규모는 schemas 그대로 · 상품 분류는 적재 스크립트(2.sql) 그대로

xplan

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |      1 |        |     18 |00:00:00.01 |      61 |
|   1 |  SORT GROUP BY       |              |      1 |     18 |     18 |00:00:00.01 |      61 |
|   2 |   NESTED LOOPS       |              |      1 |    706 |    668 |00:00:00.01 |      61 |
|*  3 |    TABLE ACCESS FULL | ITEM         |      1 |     18 |     18 |00:00:00.01 |       7 |
|*  4 |    INDEX RANGE SCAN  | ORD_ITEM_X02 |     18 |     39 |    668 |00:00:00.01 |      54 |
---------------------------------------------------------------------------------------------
   3 - filter("I"."ITEM_TYPE_CD"='100100')
   4 - access("OI"."ITEM_ID"="I"."ITEM_ID" AND "OI"."ORD_DT"=:V_DT)

모범 답안 서술

2번 줄의 access에 "ORD_DT"=:V_DT가 있지만 filter에도 또 나온다. X02는 ITEM_ID → ORD_DT 순으로 정렬되어 있고, ITEM_ID가 1 ~ 17 범위로 들어왔다. 범위 조건 뒤의 ORD_DT는 시작점(메뉴 1의 11월 15일)과 끝점(메뉴 17의 11월 15일)만 좁힐 뿐이다. 그 사이에 있는 메뉴 1의 11월 15일 이후, 메뉴 2 ~ 16의 2년 치 전부, 메뉴 17의 11월 15일 이전을 다 읽으며 filter로 거른다. 그래서 약 43만 항목, 리프 약 1,483블록을 읽고 631건을 남긴다(Buffers 1,485). 결과도 틀렸다. BETWEEN 1 AND 17은 번호 대역이지 '버거'라는 분류가 아니다. 나중에 추가된 자이언트더블버거(84)는 대역 밖이라 빠졌다. 74번 치즈스틱처럼 번호와 분류가 맞지 않는 메뉴가 이미 있으므로, 번호 대역으로 분류를 대신하면 언제든 같은 일이 생긴다. 세 가지 고침을 비교한다. ① IN (1, …, 17, 84): 메뉴마다 ITEM_ID = 값 AND ORD_DT = :v_dt로 찾으므로 18번 × 3블록 ≈ 54블록이고 결과도 맞다. 하지만 IN 목록은 버거 메뉴가 늘거나 단종될 때마다 SQL을 고쳐야 하고, 잊으면 지금처럼 조용히 빠진다. ② ITEM 조인: ITEM(5블록)에서 분류가 버거인 18행을 읽고, 메뉴마다 X02를 ITEM_ID = i.ITEM_ID AND ORD_DT = :v_dt로 찾는다(NL 조인). 일량은 ①과 같고(61블록), 메뉴가 늘어도 ITEM의 분류만 맞으면 SQL을 고칠 필요가 없다. ③ 새 인덱스 (ORD_DT, ITEM_ID, ORD_ITEM_QTY): ORD_DT =로 시작하고 ITEM_ID 범위가 바로 이어져 5블록이면 된다. 그러나 BETWEEN을 그대로 두면 84는 여전히 빠지고, 247만 행 테이블에 리프 약 8,500블록짜리 인덱스가 하나 늘어 주문상품을 INSERT할 때마다 고쳐야 하는 적재 비용이 든다. ②를 고른다. 실행계획에서 ITEM은 FULL로 한 번 읽고(3번 줄, 18행), X02는 NL 조인의 안쪽으로 18번 읽힌다(4번 줄 Starts 18). 매번 ITEM_ID와 ORD_DT가 모두 =라서 둘 다 access 조건이고 filter가 없다. Buffers는 1,485 → 61이고, 자이언트더블버거가 들어간 18줄이 나온다.

해설

IN 목록의 이득과 조인의 이득은 같은 원리다. 앞 컬럼을 =로 만들어 뒤 컬럼까지 access로 쓰게 한다. 다른 점은 값 목록이 SQL 안에 박혀 있느냐, 데이터(ITEM의 분류)에서 오느냐다. 목록이 업무 규칙이라면 데이터에 둔다.
②는 옵티마이저가 ITEM을 먼저 읽는 NL 조인을 골라야 효과가 있다. ITEM이 18행이라 대개 그렇게 고르지만, 해시 조인으로 바뀌면 X02를 FAST FULL SCAN으로 읽을 수 있다. 실행계획을 확인하고, 필요하면 LEADING(i) USE_NL(oi)를 붙이되 힌트 대장에 적는다.
새 인덱스 ③만 만들고 SQL을 그대로 두면, 빨라진 화면이 여전히 틀린 숫자를 보여 준다. 성능 문제와 정합성 문제가 한 SQL에 같이 있으면 정합성부터 고친다.
메뉴가 단종되면 ITEM의 SALE_YN이 'N'이 된다. 지난 판매를 볼 때는 단종 메뉴도 넣어야 하므로 SALE_YN 조건은 붙이지 않는다. 어떤 메뉴를 '버거류'로 볼지는 상품기획팀과 정한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 규모는 schemas 그대로 · 상품 분류는 적재 스크립트(2.sql) 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 규모는 schemas 그대로 · 상품 분류는 적재 스크립트(2.sql) 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황