JOIN-022

주문 뒤 10분 안의 적립을 찾는 점검이 포인트 테이블을 583번 읽는다

기초SELECT이커머스172 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

마케팅팀의 「적립 지연 점검」은 한 달 치 주문마다, 같은 고객에게 주문 뒤 10분 안에 들어온 적립(EARN) 거래를 붙여 본다. 화면은 이 목록과 주문 수를 비교해 적립이 늦거나 빠진 주문을 따로 띄운다. 포인트 거래에는 주문 번호가 없어서 고객과 시각으로 짝을 짓는다. SQL 위에는 -- 기간(부등호) 조건 조인은 해시 조인이 안 된다. NL 로 고정이라는 주석이 있다. 담당자가 기간을 바꿔 가며 하루 수십 번 돌린다. 아래는 2024년 6월(주문 583건)의 실행계획이다.

테이블과 인덱스

orders: 34,908행 · 193블록 · PK(order_id)만 있음 · order_date 시분초 있음 · 2024년 6월 583건
point_transactions: 45,000행 · 210블록 · PK(tx_id)만 있음 · tx_type EARN 15,000건 · USE · EXPIRE · created_at 시분초 있음
두 테이블 모두 customer_id에 인덱스가 없다 (FK는 꺼져 있다) · 통계 최신

원본 SQL

sql

SELECT /*+ LEADING(o p) USE_NL(p) */
       o.order_id, o.customer_id, o.order_date, p.tx_id, p.amount, p.created_at
  FROM orders o, point_transactions p
 WHERE o.order_date >= TO_DATE(:v_from, 'YYYYMMDD')
   AND o.order_date <  TO_DATE(:v_to, 'YYYYMMDD')
   AND p.customer_id = o.customer_id
   AND p.tx_type     = 'EARN'
   AND p.created_at >= o.order_date
   AND p.created_at <  o.order_date + 10/1440

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                    |      1 |        |    521 |00:00:00.38 |    122K |
|*  1 |  FILTER             |                    |      1 |        |    521 |00:00:00.38 |    122K |
|   2 |   NESTED LOOPS      |                    |      1 |      2 |    521 |00:00:00.38 |    122K |
|*  3 |    TABLE ACCESS FULL| ORDERS             |      1 |    573 |    583 |00:00:00.01 |     193 |
|*  4 |    TABLE ACCESS FULL| POINT_TRANSACTIONS |    583 |      1 |    521 |00:00:00.37 |    122K |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_DATE(:V_TO,'YYYYMMDD')>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - filter(("O"."ORDER_DATE">=TO_DATE(:V_FROM,'YYYYMMDD') AND "O"."ORDER_DATE"<TO_DATE(:V_TO,'YYYYMMDD')))
   4 - filter(("P"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "P"."TX_TYPE"='EARN' AND "P"."CREATED_AT">="O"."ORDER_DATE"
              AND "P"."CREATED_AT"<"O"."ORDER_DATE"+.006944444444444444444444444444444444444444))

문제

122K 블록이 어디서 나오는지 4번 줄의 Starts와 블록 수로 설명하시오.
주석의 "부등호 조건 조인은 해시 조인이 안 된다"가 이 SQL에 맞는 말인지 판단하고, 개선 SQL을 작성하시오. 개선 후 Predicate Information에서 각 조건이 어디에 쓰이는지도 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 등치 조건(customer_id)이 있으므로 해시 조인이 된다. 시각 조건은 해시 조인 안에서 거른다
SELECT /*+ LEADING(o p) USE_HASH(p) */
       o.order_id, o.customer_id, o.order_date, p.tx_id, p.amount, p.created_at
  FROM orders o, point_transactions p
 WHERE o.order_date >= TO_DATE(:v_from, 'YYYYMMDD')
   AND o.order_date <  TO_DATE(:v_to, 'YYYYMMDD')
   AND p.customer_id = o.customer_id
   AND p.tx_type     = 'EARN'
   AND p.created_at >= o.order_date
   AND p.created_at <  o.order_date + 10/1440

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

xplan

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                    |      1 |        |    521 |00:00:00.02 |     403 |       |       |          |
|*  1 |  FILTER             |                    |      1 |        |    521 |00:00:00.02 |     403 |       |       |          |
|*  2 |   HASH JOIN         |                    |      1 |      2 |    521 |00:00:00.02 |     403 |  1449K|  1449K| 1260K (0)|
|*  3 |    TABLE ACCESS FULL| ORDERS             |      1 |    573 |    583 |00:00:00.01 |     193 |       |       |          |
|*  4 |    TABLE ACCESS FULL| POINT_TRANSACTIONS |      1 |  15000 |  15000 |00:00:00.01 |     210 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_DATE(:V_TO,'YYYYMMDD')>TO_DATE(:V_FROM,'YYYYMMDD'))
   2 - access("P"."CUSTOMER_ID"="O"."CUSTOMER_ID")
       filter(("P"."CREATED_AT">="O"."ORDER_DATE" AND "P"."CREATED_AT"<"O"."ORDER_DATE"+.006944444444444444444444444444444444444444))
   3 - filter(("O"."ORDER_DATE">=TO_DATE(:V_FROM,'YYYYMMDD') AND "O"."ORDER_DATE"<TO_DATE(:V_TO,'YYYYMMDD')))
   4 - filter("P"."TX_TYPE"='EARN')

모범 답안 서술

4번 줄 TABLE ACCESS FULL POINT_TRANSACTIONS의 Starts가 583이다. 6월 주문 583건마다 포인트 거래 210블록을 처음부터 끝까지 다시 읽었으므로 583 × 210 = 122,430블록이고, 전체 122,623블록의 거의 전부다. 두 테이블 모두 customer_id에 인덱스가 없어서 NL의 inner를 찾아갈 길이 FULL 스캔뿐이었다. Predicate 4번에 조인 조건 넷이 모두 filter로 걸려 있는 것도 그 때문이다. 주석의 말은 이 SQL에 맞지 않는다. 해시 조인을 쓸 수 없는 것은 조인 조건에 등치 조건이 하나도 없을 때다. 여기에는 p.customer_id = o.customer_id라는 등치 조건이 있으므로 해시 조인을 쓸 수 있다. 해시 조인은 등치 조건으로 같은 고객의 행만 한 버킷에 모으고, 시각 조건(부등호)은 그 버킷 안에서 짝을 맞춰 볼 때 거른다. USE_NL을 USE_HASH로 바꾸면 6월 주문 583건으로 해시 테이블을 만들고 포인트 거래를 한 번만 읽는다. 개선 후 Predicate 2번을 보면 customer_id는 해시 조인의 access로, 시각 조건 두 개는 같은 줄의 filter로 쓰이고, tx_type = 'EARN'은 4번 줄에서 먼저 걸러진다. Buffers는 122,623 → 403으로 준다.

해설

해시 조인이 정말 안 되는 경우: 조인 조건이 BETWEEN이나 >처럼 부등호뿐이면 해시 함수로 짝을 모을 수 없다. 이때는 NL이나 소트 머지만 쓸 수 있다 (교재 06화 §1).
고객 한 명에 거래가 많다면: 해시 조인은 등치 조건으로 모은 뒤 버킷 안에서 부등호를 비교한다. 고객 한 명의 거래가 수만 건이면 비교가 많아져 CPU가 든다. 그때는 point_transactions (customer_id, created_at) 인덱스를 두고 NL로 시각 범위까지 access로 찾는 편이 낫다. 지금은 고객당 거래가 9건 안팎이라 해시 조인이 맞다.
다른 정답: 힌트를 모두 떼도 옵티마이저가 같은 해시 조인을 고른다. 인덱스를 만들어 NL로 푸는 안은 화면 하나를 위해 적재가 많은 포인트 테이블에 인덱스를 더하는 것이라, 호출 빈도(하루 수십 번)에 비해 대가가 크다.
빠진 적립 찾기: 이 목록은 짝이 있는 주문만 낸다. 적립이 빠진 주문을 바로 내려면 NOT EXISTS나 외부 조인으로 바꾼다. 그래도 등치 조건이 있으면 해시 조인(안티 조인, 외부 조인)으로 풀린다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황