공휴일 열여섯 날의 주문 수를 세는데 주문 100만 건을 다 읽고 해시 조인한다

중급SELECT버거30 / 700

상황

버거원 마케팅팀의 「공휴일 매출」 화면은 연도를 고르면 그해 공휴일마다 주문 수를 보여 준다. 공휴일은 본사 영업 달력 테이블에 DATE로 들어 있고, 주문 일자는 문자다. 조회가 끝나기까지 0.5초쯤 걸린다. POS DB의 접속 풀은 접속할 때마다 ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD'를 실행해서 이 SQL은 에러 없이 돈다. 같은 SQL을 SQL Developer에서 돌리면 날짜 형식 에러가 난다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) · 하루 약 1,368건
인덱스 ORD_X01 (ORD_DT, ORD_HMS): BLEVEL 2 · 리프 약 3,570블록 · 날짜순 적재 · 통계 최신
BIZ_CAL 영업 달력(이 문제에서 쓰는 파생 테이블): 731행(2012-01-01 ~ 2013-12-31) · 5블록 · CAL_DT DATE(PK) · CAL_YY VARCHAR2(4) · HOLI_YN · HOLI_NM · 2013년 공휴일 16일

원본 SQL

sql

SELECT c.CAL_DT, c.HOLI_NM, COUNT(*) AS ord_cnt
  FROM BIZ_CAL c, ORD o
 WHERE c.CAL_YY  = :v_yy
   AND c.HOLI_YN = 'Y'
   AND o.ORD_DT  = c.CAL_DT
 GROUP BY c.CAL_DT, c.HOLI_NM
 ORDER BY c.CAL_DT

실행계획 학습용 예시

xplan

[2013년]
----------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |      1 |        |     16 |00:00:00.52 |   22431 |
|   1 |  SORT GROUP BY      |         |      1 |     16 |     16 |00:00:00.52 |   22431 |
|*  2 |   HASH JOIN         |         |      1 |    160K|  21888 |00:00:00.51 |   22431 |
|*  3 |    TABLE ACCESS FULL| BIZ_CAL |      1 |     16 |     16 |00:00:00.01 |       5 |
|   4 |    TABLE ACCESS FULL| ORD     |      1 |   1000K|   1000K|00:00:00.18 |   22426 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."CAL_DT"=TO_DATE("O"."ORD_DT"))
   3 - filter(("C"."CAL_YY"=:V_YY AND "C"."HOLI_YN"='Y'))

문제

2번 줄의 TO_DATE("O"."ORD_DT")가 어디서 왔는지, 그 때문에 공휴일마다 ORD_X01로 찾아가는 NL 조인을 쓸 수 없는 이유를 설명하시오.
조인 조건을 고쳐 공휴일 16일의 주문만 읽도록 하시오. 이 SQL이 접속 풀의 NLS 설정에 기대고 있다는 점도 함께 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 형변환은 바깥 쪽(달력) 값에 : DATE를 주문 일자 모양 'YYYYMMDD'로 바꾼다 · ORD_DT는 그대로
--   포맷을 밝혔으므로 접속 세션의 NLS와 상관없이 돈다
SELECT c.CAL_DT, c.HOLI_NM, COUNT(*) AS ord_cnt
  FROM BIZ_CAL c, ORD o
 WHERE c.CAL_YY  = :v_yy
   AND c.HOLI_YN = 'Y'
   AND o.ORD_DT  = TO_CHAR(c.CAL_DT, 'YYYYMMDD')
 GROUP BY c.CAL_DT, c.HOLI_NM
 ORDER BY c.CAL_DT

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

xplan

----------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |      1 |        |     16 |00:00:00.01 |     135 |
|   1 |  SORT GROUP BY      |         |      1 |     16 |     16 |00:00:00.01 |     135 |
|   2 |   NESTED LOOPS      |         |      1 |  21888 |  21888 |00:00:00.01 |     135 |
|*  3 |    TABLE ACCESS FULL| BIZ_CAL |      1 |     16 |     16 |00:00:00.01 |       5 |
|*  4 |    INDEX RANGE SCAN | ORD_X01 |     16 |   1368 |  21888 |00:00:00.01 |     130 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("C"."CAL_YY"=:V_YY AND "C"."HOLI_YN"='Y'))
   4 - access("O"."ORD_DT"=TO_CHAR("C"."CAL_DT",'YYYYMMDD'))

모범 답안 서술

2번 줄의 조인 조건은 "C"."CAL_DT"=TO_DATE("O"."ORD_DT")다. SQL에는 o.ORD_DT = c.CAL_DT라고만 썼다. DATE와 문자를 비교하면 오라클은 문자 쪽을 날짜로 바꾸는데, 이 조인에서 문자 쪽은 주문 테이블의 컬럼 ORD_DT다. 안쪽 테이블의 조인 컬럼이 가공되었으므로 공휴일마다 ORD_X01에서 그날을 찾아가는 NL 조인을 쓸 수 없다. 옵티마이저는 하는 수 없이 주문 100만 건을 전부 읽어 행마다 TO_DATE를 하며 해시 조인했다(Buffers 22,431). 게다가 이 TO_DATE에는 포맷이 없다. 접속 풀이 세션마다 NLS_DATE_FORMAT = 'YYYYMMDD'를 걸어 주는 덕분에 '20130101'이 날짜로 읽히는 것이다. SQL Developer처럼 NLS가 다른 세션에서 돌리면 날짜 형식 에러가 난다. 빠르고 느린 문제 이전에 언제든 깨질 수 있는 SQL이다. 형변환은 바깥 쪽 값에 한다. 먼저 읽는 달력의 CAL_DT를 TO_CHAR(c.CAL_DT, 'YYYYMMDD')로 주문 일자 모양으로 바꾸면 ORD_DT는 그대로 남는다. 공휴일 16일마다 ORD_X01에서 그날 범위만 찾아가는 NL 조인이 되고(4번 줄 Starts 16), 주문 수만 세므로 테이블에 가지 않고 인덱스만 읽는다(Buffers 22,431 → 135). 포맷을 밝혔으니 NLS에도 기대지 않는다.

해설

조인에서 형변환이 붙는 자리: 묵시적 형변환은 타입 규칙대로 문자 쪽에 붙는다. 그 문자 쪽이 안쪽(나중에 찾아가는) 테이블의 조인 컬럼이면 안쪽 인덱스가 막히고, 바깥 쪽이면 아무 문제가 없다. 조인 키의 타입이 다르면 어느 쪽에 변환을 둘지 SQL에 직접 적는다.
원본에 TO_DATE(o.ORD_DT, 'YYYYMMDD')를 적어 NLS 문제만 없애면 에러는 사라지지만, 컬럼 가공은 그대로라 여전히 100만 건을 읽는다.
근본적으로는 달력 테이블에 주문과 같은 모양의 문자 컬럼(CAL_YMD VARCHAR2(8))을 두고 그것으로 조인한다. 조인 키의 타입이 같으면 형변환을 고민할 일이 없다.
해시 조인이 나쁜 것은 아니다. 공휴일이 아니라 한 해 365일을 모두 센다면 주문 50만 건을 읽어야 하므로 해시 조인이 맞을 수 있다. 여기서는 16일이라 NL 조인이 싸다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황