쿠폰 대상 400명을 뽑는 조회가 평가 테이블을 2,000번 읽는다

기초SELECT한빛유통254 / 700

상황

CS팀의 「사과 쿠폰 대상」 화면은 상품 평가에 1점을 남긴 고객을 뽑아 쿠폰 대상으로 올린다. 상담원들이 하루 수백 번 새로 고치는데 한 번에 0.3초가 걸린다. SQL을 짠 조민우는 "EXISTS 안에 ROWNUM = 1을 넣어서 한 건만 찾으면 멈추게 했다"라고 한다.

테이블과 인덱스

M_CUS 고객: 2,000행 · 30블록 · PK_M_CUS (CUS_ID)
T_ITM_EVL 상품평가: 5,000행 · 48블록 · PK_T_ITM_EVL (ITM_ID, EVL_LST_NO)만 있고 CUS_ID 인덱스는 없다
고객 한 명이 평가를 2~3건 남겼고, 한 고객의 평가는 점수가 모두 같다 · 1점 평가 1,000건 · 1점을 남긴 고객 400명
평가는 EVL_LST_NO 순으로 쌓여 있어서, 고객번호가 클수록 그 고객의 첫 평가가 테이블 뒤쪽에 있다 · 통계 최신

원본 SQL

sql

SELECT c.CUS_ID, c.CUS_NM, c.CUS_GD
  FROM M_CUS c
 WHERE EXISTS (SELECT 1
                 FROM T_ITM_EVL e
                WHERE e.CUS_ID = c.CUS_ID
                  AND e.EVL_PT = 1
                  AND ROWNUM = 1)          -- 한 건 찾으면 멈추게

실행계획 학습용 예시

xplan

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |    400 |00:00:00.31 |   80842 |
|*  1 |  FILTER             |           |      1 |        |    400 |00:00:00.31 |   80842 |
|   2 |   TABLE ACCESS FULL | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |
|*  3 |   COUNT STOPKEY     |           |   2000 |        |    400 |00:00:00.30 |   80812 |
|*  4 |    TABLE ACCESS FULL| T_ITM_EVL |   2000 |      1 |    400 |00:00:00.30 |   80812 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT 0 FROM "T_ITM_EVL" "E" WHERE ROWNUM=1 AND
              "E"."CUS_ID"=:B1 AND "E"."EVL_PT"=1))
   3 - filter(ROWNUM=1)
   4 - filter(("E"."CUS_ID"=:B1 AND "E"."EVL_PT"=1))

문제

3번과 4번 줄의 Starts가 2,000인 이유와 80,812블록이 어디서 나오는지 설명하시오. 서브쿼리가 조인으로 펼쳐지지 않은 이유도 설명하시오.
결과가 바뀌지 않게 SQL을 고치고, 고친 뒤의 처리 방식을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- EXISTS 는 원래 첫 행에서 멈춘다. 안쪽의 ROWNUM 만 지운다
SELECT c.CUS_ID, c.CUS_NM, c.CUS_GD
  FROM M_CUS c
 WHERE EXISTS (SELECT 1
                 FROM T_ITM_EVL e
                WHERE e.CUS_ID = c.CUS_ID
                  AND e.EVL_PT = 1);
-- IN (SELECT e.CUS_ID FROM T_ITM_EVL e WHERE e.EVL_PT = 1) 로 써도 같은 계획이다

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

xplan

-------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |      1 |        |    400 |00:00:00.01 |      78 |          |
|*  1 |  HASH JOIN RIGHT SEMI|           |      1 |    400 |    400 |00:00:00.01 |      78 | 1316K (0)|
|*  2 |   TABLE ACCESS FULL  | T_ITM_EVL |      1 |   1000 |   1000 |00:00:00.01 |      48 |          |
|   3 |   TABLE ACCESS FULL  | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."CUS_ID"="C"."CUS_ID")
   2 - filter("E"."EVL_PT"=1)

모범 답안 서술

1번 줄이 FILTER이고, 그 아래 3번과 4번 줄의 Starts가 2,000이다. 서브쿼리가 조인으로 펼쳐지지 않아서 고객 2,000명을 읽어 가며 고객마다 평가 테이블을 처음부터 다시 읽었다. 고객번호는 고객마다 하나뿐이라 FILTER의 캐시(메모판)도 소용이 없다. 1점을 남기지 않은 고객 1,600명은 평가 48블록을 끝까지 읽고 나서야 '없다'를 알게 되므로 76,800블록이 여기서 나오고, 1점 고객 400명이 첫 평가를 찾을 때까지 읽은 4,012블록을 더해 80,812블록이 된다. 원인은 서브쿼리 안의 ROWNUM = 1이다. ROWNUM은 '나오는 순서대로 번호를 매긴다'는 뜻이라, 서브쿼리를 바깥과 합쳐 세미 조인으로 펼치면 번호의 뜻이 달라진다. 그래서 옵티마이저는 이 서브쿼리를 펼치지 않고(Unnesting을 하지 않고) FILTER로 고객마다 따로 실행한다. EXISTS는 원래 조건에 맞는 첫 행을 찾으면 그 자리에서 멈춘다. ROWNUM = 1은 EXISTS가 이미 하는 일을 한 번 더 적은 것이라 지워도 결과는 같다. 지우면 옵티마이저가 서브쿼리를 해시 세미 조인(HASH JOIN RIGHT SEMI)으로 펼친다. 1점 평가 1,000건(48블록)을 한 번 읽어 해시 테이블에 올리고, 고객 2,000명(30블록)을 한 번 훑으면서 짝이 있는 고객만 한 번씩 내보낸다. Buffers는 80,842 → 78로 주고, 결과는 400명 그대로다.

해설

같은 습관, 반대 결과: 메인 쿼리의 WHERE ROWNUM <= 10은 부분범위 처리로 일을 줄인다. 서브쿼리 안의 ROWNUM은 반대로 쿼리 변환을 막아 일을 늘린다. EXISTS 안에는 ROWNUM을 넣지 않는다.
다른 정답: IN (SELECT e.CUS_ID FROM T_ITM_EVL e WHERE e.EVL_PT = 1)로 써도 같은 해시 세미 조인이 된다. 평가에 (CUS_ID, EVL_PT) 인덱스를 만들면 FILTER로 남아도 고객마다 2블록 남짓(약 4,000블록)이지만, 펼치는 쪽이 인덱스 없이도 더 싸다.
변환을 막는 다른 모양: NO_UNNEST 힌트도 같은 FILTER를 만든다(SUBQ-002). 힌트가 없는데 FILTER가 보이면 서브쿼리 안에 ROWNUM이나 행 제한 절(FETCH FIRST)이 있는지부터 본다(교재 08화 §4).
FILTER의 캐시는 같은 입력 값이 되풀이될 때만 듣는다. 바깥이 고객 마스터처럼 키가 유일한 집합이면 Starts는 바깥 행 수와 같다(교재 07화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황