주문 상태 화면 넷을 인덱스 하나로 받치겠다는 설계안을 세션에서 재 보니 한 화면이 4천 블록을 넘게 읽는다

중급SELECT한빛유통72 / 700

상황

조민우가 주문 상태와 관련된 화면 넷을 인덱스 하나로 받치겠다며 IX_ORD_BIG_ST_CUS_DT (ORD_ST, CUS_ID, ORD_DT)를 INVISIBLE로 만들었다. 메모는 이렇다. "선두 ORD_ST는 3종뿐이라, 상태 조건이 없는 SQL도 SKIP SCAN으로 세 번만 들어가면 된다." 세션에서 재 보니 Q1, Q2는 몇 블록이면 끝나는데 Q3은 108블록, Q4는 4,381블록이다. 박팀장은 SQL마다 이 인덱스를 어떻게 쓰는지 판정해 오고, 인덱스를 하나만 더 만들 수 있다면 무엇을 만들지 정해 오라고 했다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_ST COMPLETE 900,000 · PENDING 80,000 · CANCEL 20,000 · CUS_ID 2,000명(고객당 500건, 상태가 섞여 있다) · ORD_DT 1,095일(하루 913건, 전부 자정)
2024-06-14: 913건 · 그중 CANCEL 18건(테이블의 서로 다른 블록에 있다)
조민우 안 IX_ORD_BIG_ST_CUS_DT (ORD_ST, CUS_ID, ORD_DT): INVISIBLE · BLEVEL 2 · 리프 약 4,350블록(리프 한 블록에 약 230항목) · CANCEL 2만 항목은 리프 약 87블록 · 통계 최신
이 문제에서는 다른 인덱스가 없다 · 주문은 운영에서 계속 INSERT된다(인덱스 하나에 5만 건당 redo 약 12MB, 교재 14화)

원본 SQL

sql

-- Q1 상담 화면 「고객 미결제 주문」 (하루 약 3천 번)
SELECT ORD_SEQ, ORD_DT, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_ST = 'PENDING'
   AND CUS_ID = :v_cus
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD');

-- Q2 상담 화면 「고객 상태별 주문 수」 (하루 약 3천 번)
SELECT ORD_ST, COUNT(*) AS ord_cnt
  FROM T_ORD_BIG
 WHERE CUS_ID = :v_cus
 GROUP BY ORD_ST;

-- Q3 정산팀 「일 취소 목록」 (하루 수십 번)
SELECT ORD_SEQ, CUS_ID, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_ST = 'CANCEL'
   AND ORD_DT = TO_DATE(:v_dt, 'YYYYMMDD');

-- Q4 운영 상황판 「오늘 주문 건수」 (1분마다)
SELECT COUNT(*)
  FROM T_ORD_BIG
 WHERE ORD_DT = TO_DATE(:v_dt, 'YYYYMMDD');

-- 조민우 안
CREATE INDEX IX_ORD_BIG_ST_CUS_DT ON T_ORD_BIG (ORD_ST, CUS_ID, ORD_DT) INVISIBLE;

실행계획 학습용 예시

xplan

[Q3 · 2024-06-14 · 세션에서 잰 것]
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |      1 |        |     18 |00:00:00.01 |     108 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG            |      1 |     18 |     18 |00:00:00.01 |     108 |
|*  2 |   INDEX RANGE SCAN                  | IX_ORD_BIG_ST_CUS_DT |      1 |     18 |     18 |00:00:00.01 |      90 |
----------------------------------------------------------------------------------------------------------------------
   2 - access("ORD_ST"='CANCEL' AND "ORD_DT"=TO_DATE(:V_DT,'YYYYMMDD'))
       filter("ORD_DT"=TO_DATE(:V_DT,'YYYYMMDD'))

[Q4 · 2024-06-14 · 세션에서 잰 것]
-------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                      |      1 |        |      1 |00:00:00.08 |    4381 |
|   1 |  SORT AGGREGATE       |                      |      1 |      1 |      1 |00:00:00.08 |    4381 |
|*  2 |   INDEX FAST FULL SCAN| IX_ORD_BIG_ST_CUS_DT |      1 |    913 |    913 |00:00:00.08 |    4381 |
-------------------------------------------------------------------------------------------------------
   2 - filter("ORD_DT"=TO_DATE(:V_DT,'YYYYMMDD'))

[Q1 · CUS_000123]  INDEX RANGE SCAN · access("ORD_ST"='PENDING' AND "CUS_ID"=:V_CUS AND "ORD_DT">=…) · 테이블 3행 · Buffers 6
[Q2 · CUS_000123]  INDEX SKIP SCAN · access("CUS_ID"=:V_CUS) filter("CUS_ID"=:V_CUS) · 인덱스만 · Buffers 11

문제

Q1 ~ Q4가 이 인덱스를 각각 어떻게 쓰는지(access, filter, SKIP SCAN, FAST FULL SCAN) 판정하고, 그렇게 되는 이유를 설명하시오.
같은 SKIP SCAN 메모가 Q2에는 맞고 Q4에는 맞지 않는 이유를, 건너뛰어야 하는 앞 컬럼들의 조합 수로 설명하시오.
인덱스를 하나만 더 만들 수 있다면 무엇을 만들지 DDL로 작성하고, Q3과 Q4가 어떻게 바뀌는지 숫자로 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- Q1 · Q2 는 조민우 안이 맡는다 (Q2 는 선두 3 종을 건너뛴다)
-- Q3 · Q4 : 둘 다 = 로 주는 ORD_DT 를 선두로, Q3 의 ORD_ST 를 둘째로
CREATE INDEX IX_ORD_BIG_DT_ST ON T_ORD_BIG (ORD_DT, ORD_ST) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- Q1 ~ Q4 를 세션에서 다시 잰다
-- T2 승인 뒤 두 인덱스를 함께 VISIBLE 로
ALTER INDEX IX_ORD_BIG_ST_CUS_DT VISIBLE;
ALTER INDEX IX_ORD_BIG_DT_ST VISIBLE;
-- Q1 ~ Q4 의 SQL 은 그대로

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

xplan

[Q3 · 고친 뒤]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |      1 |        |     18 |00:00:00.01 |      21 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG        |      1 |     18 |     18 |00:00:00.01 |      21 |
|*  2 |   INDEX RANGE SCAN                  | IX_ORD_BIG_DT_ST |      1 |     18 |     18 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------------
   2 - access("ORD_DT"=TO_DATE(:V_DT,'YYYYMMDD') AND "ORD_ST"='CANCEL')

[Q4 · 고친 뒤]
--------------------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |      1 |        |      1 |00:00:00.01 |       6 |
|   1 |  SORT AGGREGATE   |                  |      1 |      1 |      1 |00:00:00.01 |       6 |
|*  2 |   INDEX RANGE SCAN| IX_ORD_BIG_DT_ST |      1 |    913 |    913 |00:00:00.01 |       6 |
--------------------------------------------------------------------------------------------------
   2 - access("ORD_DT"=TO_DATE(:V_DT,'YYYYMMDD'))

모범 답안 서술

Q1은 세 컬럼이 모두 조건에 있어 세 컬럼 모두 access다. PENDING 안에서 그 고객을, 그 고객 안에서 기간 범위를 바로 찾아간다(6블록). Q2는 선두 ORD_ST의 조건이 없지만 값이 3종뿐이다. SKIP SCAN으로 COMPLETE, PENDING, CANCEL 덩어리에 한 번씩 들어가 그 고객의 항목을 찾고 인덱스 안에서 센다(11블록). 메모가 맞는 것은 여기까지다. Q3은 ORD_ST = 'CANCEL'은 access지만 가운데 CUS_ID의 조건이 없다. CANCEL 덩어리 안에서 항목은 고객 순이고 날짜는 고객마다 따로 정렬되어 있으므로, 2024-06-14의 18건은 2만 항목 사이에 흩어져 있다. 그래서 ORD_DT는 access에 적혀 있어도 범위를 좁히지 못하고, CANCEL 항목 2만 개(리프 87블록)를 다 읽으며 거르는 filter다(108블록). 가운데 컬럼이 비면 그 뒤 컬럼은 범위를 정하지 못한다. Q4는 앞 두 컬럼이 다 빠졌다. SKIP SCAN을 하려면 (ORD_ST, CUS_ID) 조합마다 들어가야 하는데 조합이 3 × 2,000 = 6,000개다. 그래서 옵티마이저는 SKIP SCAN 대신 인덱스를 통째로 훑는 FAST FULL SCAN을 골랐다(4,381블록). 1분마다 도므로 하루 630만 블록이다. SKIP SCAN의 비용은 선두 컬럼 하나의 종류가 아니라 건너뛰는 앞 컬럼들의 조합 수에 비례한다. Q2는 3, Q4는 6,000이다. Q3과 Q4는 둘 다 ORD_DT를 등치로 준다. (ORD_DT, ORD_ST)를 하나 더 만들면 Q3은 두 컬럼이 다 access라 그날 CANCEL 18건만 찾아가고(108 → 21), Q4는 그날 항목 913개만 센다(4,381 → 6). 대가는 주문 INSERT마다 고칠 인덱스가 하나 느는 것(5만 건당 redo 약 12MB)이다.

해설

같은 세 컬럼이라도 순서가 조합 수를 정한다. ACC-039는 (ORD_ST, ORD_DT, CUS_ID)로 Q2 모양의 SQL에서 1,533 조합을 건너뛰었고, 이 문제의 (ORD_ST, CUS_ID, ORD_DT)는 3 조합이면 된다.
Q3의 Predicate에 ORD_DT가 access와 filter에 함께 적힌 모양은 "access에 적혔지만 범위는 못 좁혔다"는 뜻이다. access에 있다고 다 찾아간 조건은 아니다. Buffers와 리프 수로 확인한다.
Q4만 보면 (ORD_DT) 하나로도 된다. Q3까지 받치려고 ORD_ST를 붙였고, 「일 상태별 건수」 같은 SQL도 이 인덱스로 받칠 수 있다.
「오늘 주문 건수」를 1분마다 세는 화면이라면 집계 테이블이나 결과 캐시도 따져 볼 수 있다. 인덱스로 6블록이면 그럴 필요는 거의 없다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황