하루 주문 1,368건의 품목 수를 붙이는 마감 점검이 주문상품 인덱스를 1,368번 찾아간다

중급SELECT버거285 / 700

상황

버거 본사의 「일 마감 주문 점검」은 날짜를 고르면 그날 전 매장 주문을 주문별 품목 수와 수량 합계와 함께 보여 준다. 마감 담당이 매일 밤 여러 번 열고, 감사팀은 지난 날짜를 하루에 수백 번 넘겨 본다. DBA가 보니 실행계획에 VIEW PUSHED PREDICATE가 있었다. 오과장은 "조인 조건을 뷰 안으로 밀어 넣는 좋은 변환이라던데 5,500블록이나 읽는다. 그날 주문상품은 3,381줄뿐이다"라고 한다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_X01 (ORD_DT, ORD_HMS): 날짜순 적재 · 하루치 약 1,370건이 31블록에 모여 있다
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) 높이 3 · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · 주문번호 순(곧 날짜순)으로 쌓여 하루치가 약 76블록에 모여 있다 · 한 주문의 상품은 한 블록에 있다
ORD_ITEM의 ORD_DT와 ORD_HMS는 주문 헤더의 값을 그대로 복사한 컬럼이다(적재 프로그램이 함께 넣는다)
2013-03-15 주문 1,368건 · 주문상품 3,381줄 · 통계 최신

원본 SQL

sql

SELECT o.ORD_NO, o.SHOP_NO, o.ORD_HMS, v.ITEM_CNT, v.QTY
  FROM ORD o,
       (SELECT ORD_NO, COUNT(*) AS ITEM_CNT, SUM(ORD_ITEM_QTY) AS QTY
          FROM ORD_ITEM
         GROUP BY ORD_NO) v
 WHERE o.ORD_DT = :dt
   AND v.ORD_NO = o.ORD_NO
 ORDER BY o.SHOP_NO, o.ORD_HMS

실행계획 학습용 예시

xplan

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |      1 |        |   1368 |00:00:00.05 |    5509 |          |
|   1 |  SORT ORDER BY                        |             |      1 |   1370 |   1368 |00:00:00.05 |    5509 |  124K (0)|
|   2 |   NESTED LOOPS                        |             |      1 |   1370 |   1368 |00:00:00.05 |    5509 |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD         |      1 |   1370 |   1368 |00:00:00.01 |      37 |          |
|*  4 |     INDEX RANGE SCAN                  | ORD_X01     |      1 |   1370 |   1368 |00:00:00.01 |       6 |          |
|   5 |    VIEW PUSHED PREDICATE              |             |   1368 |      1 |   1368 |00:00:00.04 |    5472 |          |
|   6 |     SORT GROUP BY                     |             |   1368 |      1 |   1368 |00:00:00.03 |    5472 |          |
|   7 |      TABLE ACCESS BY INDEX ROWID      | ORD_ITEM    |   1368 |      2 |   3381 |00:00:00.02 |    5472 |          |
|*  8 |       INDEX RANGE SCAN                | ORD_ITEM_PK |   1368 |      2 |   3381 |00:00:00.01 |    4104 |          |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("O"."ORD_DT"=:DT)
   8 - access("ORD_NO"="O"."ORD_NO")

문제

5번부터 8번 줄이 무엇을 몇 번 했는지, 5,472블록이 어떻게 나오는지 설명하시오.
결과는 그대로 두고, 주문상품을 그날 범위만 한 번에 읽도록 SQL을 고치시오. 날짜 조건이 뷰 안으로 저절로 들어가지 않은 이유도 쓰시오.
고친 방식보다 지금의 VIEW PUSHED PREDICATE가 나은 경우를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 주문상품에도 같은 날짜가 있다: 뷰 안에서 그날 범위만 한 번에 읽고 묶는다
SELECT o.ORD_NO, o.SHOP_NO, o.ORD_HMS, v.ITEM_CNT, v.QTY
  FROM ORD o,
       (SELECT ORD_NO, COUNT(*) AS ITEM_CNT, SUM(ORD_ITEM_QTY) AS QTY
          FROM ORD_ITEM
         WHERE ORD_DT = :dt
         GROUP BY ORD_NO) v
 WHERE o.ORD_DT = :dt
   AND v.ORD_NO = o.ORD_NO
 ORDER BY o.SHOP_NO, o.ORD_HMS

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

xplan

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |              |      1 |        |   1368 |00:00:00.01 |     127 |          |
|   1 |  SORT ORDER BY                          |              |      1 |   1370 |   1368 |00:00:00.01 |     127 |  124K (0)|
|*  2 |   HASH JOIN                             |              |      1 |   1370 |   1368 |00:00:00.01 |     127 | 1312K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED  | ORD          |      1 |   1370 |   1368 |00:00:00.01 |      37 |          |
|*  4 |     INDEX RANGE SCAN                    | ORD_X01      |      1 |   1370 |   1368 |00:00:00.01 |       6 |          |
|   5 |    VIEW                                 |              |      1 |   1370 |   1368 |00:00:00.01 |      90 |          |
|   6 |     HASH GROUP BY                       |              |      1 |   1370 |   1368 |00:00:00.01 |      90 | 1150K (0)|
|   7 |      TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM     |      1 |   3387 |   3381 |00:00:00.01 |      90 |          |
|*  8 |       INDEX RANGE SCAN                  | ORD_ITEM_X01 |      1 |   3387 |   3381 |00:00:00.01 |      14 |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("V"."ORD_NO"="O"."ORD_NO")
   4 - access("O"."ORD_DT"=:DT)
   8 - access("ORD_DT"=:DT)

모범 답안 서술

5번 줄 VIEW PUSHED PREDICATE는 바깥의 조인 조건 ORD_NO = o.ORD_NO를 뷰 안으로 밀어 넣은 모양(JPPD)이다. 그래서 주문 1,368건마다 뷰를 한 번씩 열었다(Starts 1,368). 한 번 열 때마다 ORD_ITEM_PK를 높이만큼 3블록 내려가 그 주문의 상품 2~3줄을 찾고(8번 줄 4,104블록), 한 블록에 모인 상품 행을 읽어(1,368블록) 묶는다. 합이 5,472블록이다. 그날 주문상품은 76블록에 모여 있는데, 주문마다 인덱스 위에서부터 다시 내려가느라 70배 넘게 읽었다. 주문상품에도 ORD_DT가 있고 헤더 값을 그대로 복사한 컬럼이다. 하지만 SQL에는 ORD_ITEM.ORD_DT와 ORD.ORD_DT를 잇는 조건이 없으므로, 옵티마이저는 o.ORD_DT = :dt에서 ORD_ITEM.ORD_DT = :dt를 끌어낼(조건 이행) 수 없다. 두 값이 같다는 것은 적재 프로그램만 아는 사실이다. 그래서 날짜 조건을 뷰 안에 직접 준다. 뷰는 ORD_ITEM_X01로 그날 3,381줄만 한 번에 읽어(14 + 76블록) 주문별로 묶고, 그 1,368줄을 주문 1,368건과 해시 조인한다. 결과는 같고 Buffers는 5,509 → 127로 준다. JPPD가 나은 경우도 있다. 매장 한 곳의 하루 주문(약 7건)처럼 바깥 집합이 작으면 JPPD가 낫다. 7번 × 4블록 = 28블록으로 끝나 하루치를 다 묶는 쪽(약 90블록)보다 싸다. 바깥이 수천 건이 되면 행마다 뷰를 여는 비용이 범위를 한 번 읽는 비용을 넘는다.

해설

조건 이행으로 저절로 들어가게 하는 법: 뷰를 GROUP BY ORD_NO, ORD_DT로 묶고 v.ORD_DT = o.ORD_DT로도 조인하면, 옵티마이저가 o.ORD_DT = :dt에서 v.ORD_DT = :dt를 끌어내 뷰 안으로 밀어 넣는다(조건절 이행 + 조건절 Pushing). 조건을 한 번만 적어도 되는 대신 SQL이 길어진다.
함정: 복사 컬럼이 헤더와 어긋난 행이 있으면(자정을 넘겨 수정한 주문 등) 두 SQL의 결과가 달라진다. 바꾸기 전에 ORD와 ORD_ITEM의 날짜가 다른 행이 0건인지 확인하고, 고친 뒤 건수와 수량 합계, MINUS로 대조한다(교재 08화).
JPPD와 복합 뷰 머징은 서로 대신한다: 힌트가 없으면 옵티마이저는 JPPD나 뷰를 풀어 조인한 뒤 묶는 모양(VM_NWVW_) 중 싼 쪽을 고른다. 두 모양 모두 주문마다 ORD_ITEM_PK를 찾아가므로 이 화면에서는 비슷하게 무겁다.
계획을 읽을 때 VIEW PUSHED PREDICATE의 Starts를 바깥 행 수와 비교한다. Starts × 한 번의 블록 수가 범위를 한 번 읽는 블록 수보다 크면 다른 길을 찾는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황