점주 앱에서 매장 두 곳을 고르면 오늘 주문이 5천 건 넘게 뜨고 화면이 멈춘다

기초SELECT버거50 / 700

상황

버거 점주 앱의 「오늘 주문」은 점주가 고른 매장의 그날 주문을 시각(시:분)순으로 보여 준다. 매장 하나일 때는 문제가 없었는데, 매장 두 곳을 가진 점주를 위해 개발자가 조건 끝에 OR를 덧붙였다. 그 뒤로 매장 둘을 고르면 주문이 5천 건 넘게 뜨고 화면이 몇 초씩 멈춘다. 개발자는 "매장 하나로 테스트할 때는 결과가 맞았다"고 한다. 아래는 2013-03-15에 SH0012와 SH0057을 골랐을 때다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_NO · ORD_DT VARCHAR2(8) · ORD_HMS · SHOP_NO 'SH0001'(매장 200곳, 매장마다 2년에 약 5,000건, 하루 약 7건) · 주문 일자 순으로 쌓였다
인덱스 ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS) · ORD_X02 (SHOP_NO, ORD_DT): BLEVEL 2 · 통계 최신
2013-03-15 주문: SH0012 7건 · SH0057 8건 · SH0057의 2년 치 주문 5,012건
매장을 하나만 고르면 앱은 :v_shop2에 NULL을 넘긴다

원본 SQL

sql

SELECT SHOP_NO, SUBSTR(ORD_HMS, 1, 4) AS hhmi, ORD_NO
  FROM ORD
 WHERE ORD_DT  = :v_dt
   AND SHOP_NO = :v_shop1
    OR SHOP_NO = :v_shop2
 ORDER BY ORD_HMS

실행계획 학습용 예시

xplan

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name            | Starts | E-Rows | A-Rows | A-Time      | Buffers |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                 |      1 |        |   5019 | 00:00:00.09 |    5041 |
|   1 |  SORT ORDER BY                          |                 |      1 |   5014 |   5019 | 00:00:00.09 |    5041 |
|   2 |   VIEW                                  | VW_ORE_1C6B3F07 |      1 |   5014 |   5019 | 00:00:00.08 |    5041 |
|   3 |    UNION-ALL                            |                 |      1 |        |   5019 | 00:00:00.08 |    5041 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED | ORD             |      1 |      7 |      7 | 00:00:00.01 |      10 |
|*  5 |      INDEX RANGE SCAN                   | ORD_X02         |      1 |      7 |      7 | 00:00:00.01 |       3 |
|*  6 |     TABLE ACCESS BY INDEX ROWID BATCHED | ORD             |      1 |   5000 |   5012 | 00:00:00.08 |    5031 |
|*  7 |      INDEX RANGE SCAN                   | ORD_X02         |      1 |   5000 |   5012 | 00:00:00.01 |      17 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("SHOP_NO"=:V_SHOP1 AND "ORD_DT"=:V_DT)
   6 - filter((LNNVL("ORD_DT"=:V_DT) OR LNNVL("SHOP_NO"=:V_SHOP1)))
   7 - access("SHOP_NO"=:V_SHOP2)

문제

매장 두 곳을 골랐을 때 5,019건이 나온 이유를 WHERE 절의 연산자 우선순위와 실행계획의 두 갈래(5번 줄과 7번 줄)로 설명하시오.
두 매장의 그날 주문만 인덱스로 찾아가도록 SQL을 고치고, 고친 뒤 실행계획에 나타날 연산을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT SHOP_NO, SUBSTR(ORD_HMS, 1, 4) AS hhmi, ORD_NO
  FROM ORD
 WHERE ORD_DT  = :v_dt
   AND SHOP_NO IN (:v_shop1, :v_shop2)      -- 날짜 조건이 두 매장 모두에 걸린다 · 매장마다 (SHOP_NO =, ORD_DT =)
 ORDER BY ORD_HMS

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

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Starts | E-Rows | A-Rows | A-Time      | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |      1 |        |     15 | 00:00:00.01 |      21 |
|   1 |  SORT ORDER BY                         |         |      1 |     14 |     15 | 00:00:00.01 |      21 |
|   2 |   INLIST ITERATOR                      |         |      1 |        |     15 | 00:00:00.01 |      21 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED | ORD     |      2 |     14 |     15 | 00:00:00.01 |      21 |
|*  4 |     INDEX RANGE SCAN                   | ORD_X02 |      2 |     14 |     15 | 00:00:00.01 |       6 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access((("SHOP_NO"=:V_SHOP1 OR "SHOP_NO"=:V_SHOP2)) AND "ORD_DT"=:V_DT)

모범 답안 서술

WHERE 절에서 AND는 OR보다 먼저 묶인다. 그래서 원본은 (ORD_DT = :v_dt AND SHOP_NO = :v_shop1) OR SHOP_NO = :v_shop2로 읽힌다. 첫 매장은 그날 주문만, 둘째 매장은 날짜 조건 없이 2년 치 전부가 걸린다. 실행계획에서도 보인다. 옵티마이저가 OR를 두 갈래로 펼쳤는데(VW_ORE_), 5번 줄 access에는 매장과 날짜가 다 있지만 7번 줄 access는 "SHOP_NO"=:V_SHOP2 하나뿐이다. 둘째 갈래가 SH0057의 2년 치 5,012건을 ORD_X02로 찾아 테이블에서 한 건씩 읽었으므로(6번 줄 5,031블록) 결과가 7 + 5,012 = 5,019건, Buffers가 5,041이 됐다. 화면이 멈춘 것은 5천 건을 받아 그렸기 때문이다. 같은 컬럼의 값 여러 개는 IN 목록으로 쓴다. ORD_DT = :v_dt AND SHOP_NO IN (:v_shop1, :v_shop2)이면 날짜 조건이 두 매장 모두에 걸리고, INLIST ITERATOR가 매장마다 한 번씩(4번 줄 Starts 2) SHOP_NO = 값 AND ORD_DT = :v_dt로 ORD_X02를 찾아간다. 두 컬럼이 다 access라 그날 두 매장의 15건만 읽는다(Buffers 5,041 → 21). OR로 쓰고 싶다면 (SHOP_NO = :v_shop1 OR SHOP_NO = :v_shop2)처럼 괄호로 묶어야 같은 뜻이 된다. 매장을 하나만 가진 점주는 지금처럼 :v_shop2에 NULL을 넘기면 된다. IN 목록의 NULL은 어떤 행과도 같지 않으므로 결과에 영향이 없다.

해설

함정: 이 SQL은 테스트에서 걸러지지 않기 쉽다. 매장 하나로 테스트하면 :v_shop2가 NULL이라 둘째 갈래가 0건이고 결과가 맞다. 매장 두 곳을 가진 점주가 쓰는 순간 드러난다.
IN 목록은 짝을 맞춰 준다. 날짜 하나와 매장 둘을 AND로 묶으면 (날짜, 매장1)과 (날짜, 매장2) 두 짝이 되고, 짝마다 인덱스의 한 구간을 찾아간다. 날짜도 여럿이고 매장도 여럿이면 짝이 곱으로 생기므로 원하는 짝인지 따로 따져야 한다(IDX-057).
점주가 가진 매장 수가 정해져 있지 않다면 IN 칸을 늘리기보다 점주와 매장의 대응 테이블과 조인해 매장마다 ORD_X02를 찾아가게 한다.
옵티마이저가 OR를 펼친 덕분에 원본도 FULL(22,422블록)은 피했다. 하지만 펼친 계획은 SQL에 적힌 뜻을 그대로 지킬 뿐, 사람이 의도한 뜻으로 고쳐 주지 않는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황