매장 여섯 줄짜리 판매 현황이 판매 테이블을 여섯 번 통째로 읽는다

모범 답안 SQL ​

-- 안 ① 스칼라 서브쿼리는 그대로 두고, 안쪽 조건(매장 = · 판매일자 범위)에 맞는 인덱스를 만든다
--      COUNT 는 인덱스만으로 끝난다 · 본사는 인덱스에 없으니 바로 0
CREATE INDEX CF10_X01 ON CF10 (SHP_NO, SLE_DT);

SELECT f.SHP_NO, f.SHP_NM,
       (SELECT COUNT(*)
          FROM CF10 h
         WHERE h.SHP_NO = f.SHP_NO
           AND h.SLE_DT BETWEEN '20230601' AND '20230630') AS sle_cnt
  FROM CF07 f
 ORDER BY f.SHP_NO;

-- 안 ② 인덱스 없이 : 판매를 한 번 읽어 매장별로 묶고 매장에 외부 조인 (본사 0 건 유지 · 4,587 블록)
-- SELECT f.SHP_NO, f.SHP_NM, NVL(h.sle_cnt, 0) AS sle_cnt
--   FROM CF07 f,
--        (SELECT SHP_NO, COUNT(*) AS sle_cnt FROM CF10
--          WHERE SLE_DT BETWEEN '20230601' AND '20230630' GROUP BY SHP_NO) h
--  WHERE h.SHP_NO(+) = f.SHP_NO
--  ORDER BY f.SHP_NO;

개선 후 실행계획 학습용 예시 · 건수는 적재 데이터 그대로 ​

------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |      6 |00:00:00.01 |      93 |
|   1 |  SORT AGGREGATE    |          |      6 |      1 |      6 |00:00:00.01 |      89 |
|*  2 |   INDEX RANGE SCAN | CF10_X01 |      6 |   4110 |  20953 |00:00:00.01 |      89 |
|   3 |  SORT ORDER BY     |          |      1 |      6 |      6 |00:00:00.01 |      93 |
|   4 |   TABLE ACCESS FULL| CF07     |      1 |      6 |      6 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("H"."SHP_NO"=:B1 AND "H"."SLE_DT">='20230601' AND "H"."SLE_DT"<='20230630')

모범 답안 서술 ​

2번 줄의 Starts는 6, Buffers는 27,498(= 6 × 4,583)이다. 스칼라 서브쿼리가 매장 한 줄마다 돌고, 판매 테이블에 매장번호와 판매일자로 찾을 인덱스가 없어서 한 번 돌 때마다 50만 행을 처음부터 끝까지 읽는다(TABLE ACCESS FULL). 스칼라 서브쿼리는 바깥 행마다 안쪽을 다시 찾는 NL 조인과 같다. 캐시는 같은 입력이 다시 들어올 때만 일을 덜어 주는데, 매장번호 6개는 한 번씩만 들어오므로 덜어 줄 것이 없다. 안 ①은 안쪽 조건에 맞춰 CF10_X01 (SHP_NO, SLE_DT)를 만드는 것이다. 서브쿼리 한 번이 매장의 6월 판매 약 4,200건을 인덱스만으로 세고 끝나서(리프 15블록 남짓), Buffers는 27,502 → 93이 된다. 본사는 인덱스에 없으니 2블록 만에 0을 돌려준다. 대가는 50만 행 테이블에 인덱스가 하나 늘어 판매가 들어올 때마다 인덱스도 고쳐야 한다는 것이다(판매가 하루 600 ~ 800건이라 부담은 작다). 안 ②는 인덱스 없이 판매를 한 번만 읽어(4,583블록) 매장별로 묶고 매장에 외부 조인으로 붙이는 것이다. 27,502 → 4,587블록이다. 5분마다 도는 상황판에는 안 ①이 낫고, 인덱스를 만들 수 없을 때는 안 ②를 쓴다. 어느 쪽이든 본사(1000)는 판매가 없어도 0건으로 남아야 한다. 안 ②에서 내부 조인을 쓰면 본사 줄이 사라지고, 외부 조인한 묶음 결과는 NULL이므로 NVL(…, 0)으로 0을 만든다.

해설 ​

• 스칼라 서브쿼리를 볼 때는 Starts와 함께 안쪽 줄의 접근 방식을 본다. Starts가 6처럼 작아도 안쪽이 FULL이면 6 × 테이블 전체다(교재 06화: NL 조인의 총일량은 바깥 행 수 × 안쪽 한 번의 일량).
• 인덱스 컬럼 순서: 매장번호는 =, 판매일자는 범위라서 (SHP_NO, SLE_DT) 순서로 둔다. (SLE_DT, SHP_NO)로 만들면 서브쿼리마다 6월 판매 전체를 훑으면서 매장번호를 걸러 한 번에 약 70블록을 읽는다.
• 판매번호 범위로 좁히는 안: 판매번호가 판매일자로 시작하므로 SLE_NO LIKE '202306%'로 CF10_PK 범위를 줄 수도 있다. 다만 매장 조건은 테이블에서 걸러야 하고, 판매번호 규칙에 기댄다.
• 하루 288번 × 27,502블록이면 하루 약 790만 블록이다. 작은 화면이라도 자주 돌면 숫자를 곱해 본다.

관련 교재 ​

• 06화 · NL 조인의 총일량
• 07화 · 스칼라 서브쿼리 캐시 — 칸이 정해진 메모판