교환 손님이 올 때마다 POS의 「영수증 이력」 창이 0.2초씩 멈춘다

기초SELECT유통300 / 700

상황

유통 매장 POS의 「영수증 이력」 창은 원래 영수증 한 장에서 이어진 반품 영수증과 교환 영수증을 모두 펼쳐 보여 준다. 교환한 상품을 다시 교환하는 손님도 있어서 이력은 여러 단계가 된다. 반품, 교환, 재출력 때마다 POS가 이 창을 저절로 열어 다섯 매장에서 하루 약 2,000번 돈다. 지난 분기에 반품 영수증과 교환 영수증이 원래 영수증을 가리키도록 CF10에 ORG_SLE_NO 컬럼을 더했다. 그 뒤로 창이 열릴 때마다 0.2초씩 멈춘다는 말이 매장에서 올라왔다. 아래는 2023-12-03 강남점 영수증의 이력을 펼친 것이다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO)(높이 3) · ORG_SLE_NO VARCHAR2(15): 반품·교환 영수증이 가리키는 원래 영수증 번호 · 값이 있는 행은 반품·교환 영수증 4,860장뿐이고 나머지는 NULL(뒤쪽 컬럼이 NULL이라 블록 수는 거의 그대로다) (문제 설정: 교재의 CF10에는 이 컬럼이 없다. 반품 처리를 위해 운영 중에 더한 컬럼이라고 본다)
ORG_SLE_NO에는 인덱스가 없다 · 이력은 대부분 2단계이고 길어야 4단계다 · 통계 최신
아래 영수증의 이력: 원래 영수증 1장 → 12/05 부분 반품 1장과 교환 판매 1장 → 12/09 교환 판매의 재교환 1장 → 12/15 그 반품 1장 (5장, 4단계)

원본 SQL

sql

SELECT LEVEL AS lvl, SLE_NO, ORG_SLE_NO, SLE_DT, SLE_HMS, SHP_NO
  FROM CF10
 START WITH SLE_NO = :v_sle_no
CONNECT BY PRIOR SLE_NO = ORG_SLE_NO

실행계획 학습용 예시

xplan

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      5 |00:00:00.21 |   18336 |          |
|*  1 |  CONNECT BY WITH FILTERING   |         |      1 |        |      5 |00:00:00.21 |   18336 | 2048  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| CF10    |      1 |      1 |      1 |00:00:00.01 |       4 |          |
|*  3 |    INDEX UNIQUE SCAN         | CF10_PK |      1 |      1 |      1 |00:00:00.01 |       3 |          |
|*  4 |   HASH JOIN                  |         |      4 |      2 |      4 |00:00:00.21 |   18332 | 1236K (0)|
|   5 |    CONNECT BY PUMP           |         |      4 |        |      5 |00:00:00.01 |       0 |          |
|   6 |    TABLE ACCESS FULL         | CF10    |      4 |    500K|   2000K|00:00:00.12 |   18332 |          |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ORG_SLE_NO"=PRIOR "SLE_NO")
   3 - access("SLE_NO"=:V_SLE_NO)
   4 - access("connect$_by$_pump$_002"."prior sle_no"="ORG_SLE_NO")

문제

4번과 6번 줄의 Starts 4가 무엇의 횟수인지 쓰고, 영수증 다섯 장의 이력을 펼치는 데 18,336블록을 읽은 이유를 설명하시오.
인덱스를 설계하고(DDL) 그 인덱스가 50만 행 테이블에 비해 아주 작은 이유를 쓰시오. 같은 이력을 재귀 WITH로 쓰면 인덱스 없이 해결되는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 단계마다 '앞 단계 영수증을 가리키는 영수증'을 찾는다 → 찾는 컬럼(ORG_SLE_NO)에 인덱스
--   ORG_SLE_NO 가 NULL 인 49만여 행은 인덱스에 들어가지 않는다 (반품 · 교환 4,860 장만)
CREATE INDEX CF10_X02 ON CF10 (ORG_SLE_NO);

SELECT LEVEL AS lvl, SLE_NO, ORG_SLE_NO, SLE_DT, SLE_HMS, SHP_NO
  FROM CF10
 START WITH SLE_NO = :v_sle_no
CONNECT BY PRIOR SLE_NO = ORG_SLE_NO;

-- 재귀 WITH 로 써도 같은 인덱스를 쓴다 (단계마다 c.ORG_SLE_NO = h.SLE_NO 조인)
-- WITH h (lvl, SLE_NO, ORG_SLE_NO, SLE_DT, SLE_HMS, SHP_NO) AS (
--   SELECT 1, SLE_NO, ORG_SLE_NO, SLE_DT, SLE_HMS, SHP_NO
--     FROM CF10
--    WHERE SLE_NO = :v_sle_no
--   UNION ALL
--   SELECT h.lvl + 1, c.SLE_NO, c.ORG_SLE_NO, c.SLE_DT, c.SLE_HMS, c.SHP_NO
--     FROM h, CF10 c
--    WHERE c.ORG_SLE_NO = h.SLE_NO)
-- SEARCH DEPTH FIRST BY SLE_NO SET ord
-- SELECT lvl, SLE_NO, ORG_SLE_NO, SLE_DT, SLE_HMS, SHP_NO FROM h ORDER BY ord;

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

xplan

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          |      1 |        |      5 |00:00:00.01 |      18 |
|*  1 |  CONNECT BY WITH FILTERING            |          |      1 |        |      5 |00:00:00.01 |      18 |
|   2 |   TABLE ACCESS BY INDEX ROWID         | CF10     |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  3 |    INDEX UNIQUE SCAN                  | CF10_PK  |      1 |      1 |      1 |00:00:00.01 |       3 |
|   4 |   NESTED LOOPS                        |          |      4 |      1 |      4 |00:00:00.01 |      14 |
|   5 |    CONNECT BY PUMP                    |          |      4 |        |      5 |00:00:00.01 |       0 |
|   6 |    TABLE ACCESS BY INDEX ROWID BATCHED| CF10     |      5 |      1 |      4 |00:00:00.01 |      14 |
|*  7 |     INDEX RANGE SCAN                  | CF10_X02 |      5 |      1 |      4 |00:00:00.01 |      10 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ORG_SLE_NO"=PRIOR "SLE_NO")
   3 - access("SLE_NO"=:V_SLE_NO)
   7 - access("connect$_by$_pump$_002"."prior sle_no"="ORG_SLE_NO")

모범 답안 서술

4번과 6번 줄의 Starts 4는 계층을 한 단계씩 내려간 횟수다. CONNECT BY는 앞 단계에서 찾은 영수증(5번 줄 CONNECT BY PUMP)을 받아, ORG_SLE_NO로 그 영수증을 가리키는 영수증을 찾는 일을 단계마다 되풀이한다. 원래 영수증에서 2단계(2장), 3단계(1장), 4단계(1장)를 찾고, 네 번째에는 더 내려갈 영수증이 없음을 확인하고 끝난다. ORG_SLE_NO에 인덱스가 없어서 그때마다 CF10을 통째로 읽어 해시 조인했다(6번 줄, 4 × 4,583 = 18,332블록, 200만 행). 영수증 다섯 장을 펼치는 데 18,336블록을 읽은 까닭이고, 하루 2,000번이면 약 3,670만 블록이다. 단계마다 찾는 쪽이 ORG_SLE_NO이므로 이 컬럼에 인덱스를 만든다. 그러면 단계마다 앞 단계 영수증 수만큼 인덱스를 찾아가고(NESTED LOOPS, 7번 줄 Starts 5), Buffers는 18,336 → 18이 된다. 원래 영수증을 찾는 첫 단계는 지금처럼 CF10_PK를 쓴다. 이 인덱스는 작다. B-tree 인덱스는 키가 모두 NULL인 행을 담지 않으므로, 50만 행 가운데 ORG_SLE_NO가 있는 반품과 교환 영수증 4,860장만 들어간다(리프 약 15블록, 높이 2). 판매 INSERT의 99%는 이 컬럼이 NULL이라 인덱스를 고치지 않으므로 적재 부담도 작다. 재귀 WITH로 바꿔도 인덱스는 필요하다. 재귀 WITH의 아래 갈래도 단계마다 c.ORG_SLE_NO = h.SLE_NO로 앞 단계와 조인하므로, 인덱스가 없으면 단계마다 CF10을 통째로 읽는다. 문법이 아니라 찾는 방향이 인덱스를 정한다. 거꾸로 나중 영수증에서 원래 영수증으로 올라가는 이력(CONNECT BY SLE_NO = PRIOR ORG_SLE_NO)은 단계마다 SLE_NO로 찾으므로 PK로 충분하다. 두 문법의 차이는 쓰임새에 있다. CONNECT BY는 오라클 문법이고 LEVEL, SYS_CONNECT_BY_PATH, NOCYCLE을 바로 쓸 수 있다. 재귀 WITH는 표준 SQL이라 다른 DBMS로 옮기기 쉽고, 순서와 순환 처리를 SEARCH와 CYCLE 절로 직접 적는다.

해설

한 번에 읽는 계획도 있다: 인덱스가 없으면 옵티마이저가 테이블을 한 번 읽어 메모리에서 계층을 만드는 CONNECT BY NO FILTERING WITH START-WITH를 고르기도 한다. 그래도 영수증 다섯 장을 위해 50만 행을 읽고 정렬하는 일은 남는다. 어느 모양이든 원인은 자식을 찾을 길이 없다는 것이다.
순환 데이터: 영수증 두 장이 서로를 가리키는 잘못된 데이터가 들어오면 CONNECT BY는 ORA-01436으로, 재귀 WITH는 ORA-32044로 멈춘다. NOCYCLE이나 CYCLE 절로 막고, 반품 화면에서도 자기 자신이나 이미 이력에 있는 영수증을 가리키지 못하게 한다.
결합 인덱스로 넓히는 안: (ORG_SLE_NO, SLE_NO)로 만들면 자식의 영수증 번호까지 인덱스에서 얻지만, 창이 일자, 시각, 매장도 보여 주므로 테이블에는 어차피 간다. 이력이 2 ~ 5장이라 단일 컬럼 인덱스로 충분하다.
계층 쿼리는 같은 조인을 단계 수만큼 되풀이하는 것과 같다. NL 조인과 마찬가지로 안쪽(자식)을 찾는 길이 있어야 단계마다 일이 커지지 않는다(교재 06화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황