고객 인덱스 둘을 하나로 합치자는 안을 재 보니 상담 화면 첫 칸이 24블록에서 505블록으로 늘었다

중급DDL한빛유통67 / 700

상황

고객센터 상담 화면은 고객 번호를 넣으면 세 칸을 띄운다. 이 세 SQL을 위해 T_ORD_BIG에는 고객 번호로 시작하는 인덱스가 둘 있다. 오과장은 "주문이 들어올 때마다 고객 인덱스를 둘씩 고친다. 하나로 합칠 수 없나?"라고 물었다. 조민우는 (CUS_ID, ORD_ST, ORD_DT) 하나로 합치자고 했다. INVISIBLE로 만들어 세션에서 재 보니 S2와 S3은 거의 그대로인데 S1이 24블록에서 505블록으로 늘었다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · CUS_ID 2,000명(고객당 500건, 한 고객의 주문은 테이블 곳곳의 서로 다른 블록에 있다) · ORD_ST COMPLETE 90% · PENDING 8% · CANCEL 2%(고객마다 섞여 있다) · ORD_DT 전부 자정
IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT): BLEVEL 2 · 리프 3,640블록 · S1, S3이 쓴다
IX_ORD_BIG_CUS_ST (CUS_ID, ORD_ST): ACC-039에서 S2용으로 만든 인덱스 · BLEVEL 2 · 리프 약 3,580블록
조민우 안 IX_ORD_BIG_CUS_ST_DT (CUS_ID, ORD_ST, ORD_DT): INVISIBLE · BLEVEL 2 · 리프 약 4,350블록
고객 한 명의 항목 500개는 어느 인덱스에서나 리프 2 ~ 3블록에 모여 있다
그 밖에 IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT) · 주문은 운영에서 계속 INSERT된다(인덱스 하나에 5만 건당 redo 약 12MB, 교재 14화)

원본 SQL

sql

-- S1 「최근 주문 20건」 상담 화면 첫 칸 (하루 약 1만 번)
SELECT *
  FROM (SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
          FROM T_ORD_BIG
         WHERE CUS_ID = :v_cus
         ORDER BY ORD_DT DESC)
 WHERE ROWNUM <= 20;

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

-- S3 「기간 주문 이력」 (하루 약 2천 번)
SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE CUS_ID = :v_cus
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
 ORDER BY ORD_DT DESC;

-- 조민우 안 : 재 보고 문제가 없으면 IX_ORD_BIG_CUS_DT, IX_ORD_BIG_CUS_ST 를 지운다
CREATE INDEX IX_ORD_BIG_CUS_ST_DT ON T_ORD_BIG (CUS_ID, ORD_ST, ORD_DT) INVISIBLE;

실행계획 학습용 예시

xplan

[S1 · 지금 · CUS_000123]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |      1 |        |     20 |00:00:00.01 |      24 |
|*  1 |  COUNT STOPKEY                 |                   |      1 |        |     20 |00:00:00.01 |      24 |
|   2 |   VIEW                         |                   |      1 |     20 |     20 |00:00:00.01 |      24 |
|   3 |    TABLE ACCESS BY INDEX ROWID | T_ORD_BIG         |      1 |    500 |     20 |00:00:00.01 |      24 |
|*  4 |     INDEX RANGE SCAN DESCENDING| IX_ORD_BIG_CUS_DT |      1 |    500 |     20 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------------------------------
   1 - filter(ROWNUM<=20)
   4 - access("CUS_ID"=:V_CUS)

[S1 · 조민우 안 (세션에서 잰 것) · CUS_000123]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                      |      1 |        |     20 |00:00:00.01 |     505 |
|*  1 |  COUNT STOPKEY                         |                      |      1 |        |     20 |00:00:00.01 |     505 |
|   2 |   VIEW                                 |                      |      1 |    500 |     20 |00:00:00.01 |     505 |
|*  3 |    SORT ORDER BY STOPKEY               |                      |      1 |    500 |     20 |00:00:00.01 |     505 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG            |      1 |    500 |    500 |00:00:00.01 |     505 |
|*  5 |      INDEX RANGE SCAN                  | IX_ORD_BIG_CUS_ST_DT |      1 |    500 |    500 |00:00:00.01 |       5 |
-------------------------------------------------------------------------------------------------------------------
   1 - filter(ROWNUM<=20)
   3 - filter(ROWNUM<=20)
   5 - access("CUS_ID"=:V_CUS)

[S2 · 조민우 안]  INDEX RANGE SCAN · access("CUS_ID"=:V_CUS) · 인덱스만 · Buffers 5 (지금 IX_ORD_BIG_CUS_ST로 4)
[S3 · 조민우 안]  INDEX RANGE SCAN · access("CUS_ID"=:V_CUS) filter("ORD_DT">=…) · 테이블 42행과 정렬 · Buffers 47 (지금 46)

문제

조민우 안에서 S1이 24블록에서 505블록으로 늘어난 이유를, 인덱스 안에서 한 고객의 항목이 놓인 순서로 설명하시오.
인덱스 하나로 S1 ~ S3을 모두 받치는 컬럼 순서를 정하고, 그 인덱스에서 S2와 S3이 각각 어떻게 처리되는지 설명하시오.
바꾸는 DDL과 반영 절차를 작성하고, 합쳐서 얻는 것과 잃는 것을 숫자로 적으시오.

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

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

모범답안

모범 답안 SQL

sql

-- S1 의 정렬 키 ORD_DT 를 고객 바로 뒤에 둔다 · 상태는 맨 뒤 (S2 는 고객 한 명의 항목 500 개를 인덱스만 읽어 센다)
CREATE INDEX IX_ORD_BIG_CUS_DT_ST ON T_ORD_BIG (CUS_ID, ORD_DT, ORD_ST) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- S1 ~ S3 을 세션에서 잰다
ALTER INDEX IX_ORD_BIG_CUS_DT_ST VISIBLE;
ALTER INDEX IX_ORD_BIG_CUS_DT INVISIBLE;
ALTER INDEX IX_ORD_BIG_CUS_ST INVISIBLE;     -- 한동안 다른 SQL 이 견디는지 지켜본 뒤
DROP INDEX IX_ORD_BIG_CUS_DT;
DROP INDEX IX_ORD_BIG_CUS_ST;
DROP INDEX IX_ORD_BIG_CUS_ST_DT;             -- 조민우 안은 쓰지 않는다
-- S1 ~ S3 은 그대로

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

xplan

[S1 · 고친 뒤]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                      |      1 |        |     20 |00:00:00.01 |      24 |
|*  1 |  COUNT STOPKEY                 |                      |      1 |        |     20 |00:00:00.01 |      24 |
|   2 |   VIEW                         |                      |      1 |     20 |     20 |00:00:00.01 |      24 |
|   3 |    TABLE ACCESS BY INDEX ROWID | T_ORD_BIG            |      1 |    500 |     20 |00:00:00.01 |      24 |
|*  4 |     INDEX RANGE SCAN DESCENDING| IX_ORD_BIG_CUS_DT_ST |      1 |    500 |     20 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------------------
   1 - filter(ROWNUM<=20)
   4 - access("CUS_ID"=:V_CUS)

[S2 · 고친 뒤]
------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                      |      1 |        |      3 |00:00:00.01 |       5 |
|   1 |  HASH GROUP BY    |                      |      1 |      3 |      3 |00:00:00.01 |       5 |
|*  2 |   INDEX RANGE SCAN| IX_ORD_BIG_CUS_DT_ST |      1 |    500 |    500 |00:00:00.01 |       5 |
------------------------------------------------------------------------------------------------------
   2 - access("CUS_ID"=:V_CUS)

[S3 · 고친 뒤]  INDEX RANGE SCAN DESCENDING · access("CUS_ID"=:V_CUS AND "ORD_DT">=…) · 테이블 42행 · Buffers 46

모범 답안 서술

조민우 안은 CUS_ID 다음이 ORD_ST라서, 한 고객의 항목 500개가 COMPLETE 덩어리, PENDING 덩어리, CANCEL 덩어리 순으로 놓이고 날짜는 각 덩어리 안에서만 정렬되어 있다. 최근 20건은 세 덩어리에 나뉘어 있으므로 인덱스를 거꾸로 읽어도 20건째에서 멈출 수 없다. 옵티마이저는 그 고객의 500항목을 다 읽고(5번 줄), 테이블에서 500행을 가져와(4번 줄, 500블록) 날짜로 정렬한 뒤 20건을 고른다(3번 줄). 지금 인덱스는 CUS_ID 다음이 ORD_DT라 항목이 이미 날짜 순이고, 거꾸로 읽다가 20건에서 멈춘다(COUNT STOPKEY). 그래서 24블록이 505블록이 된다. 순서를 바꿔 (CUS_ID, ORD_DT, ORD_ST)로 합친다. 정렬을 대신해야 하는 ORD_DT를 고객 바로 뒤에 두고, 세기만 하면 되는 ORD_ST는 맨 뒤에 둔다. S1은 지금과 똑같이 거꾸로 읽어 20건에서 멈춘다(24블록). S3은 CUS_ID와 ORD_DT 범위가 둘 다 access라 기간 안의 42건만 찾아간다(46블록 그대로). S2는 고객 한 명의 항목 500개를 리프 2 ~ 3블록에서 읽어 인덱스 안에서 상태별로 센다(4 → 5블록). ORD_ST가 둘째가 아니어도 되는 것은, 등치 조건인 CUS_ID가 이미 범위를 리프 몇 블록으로 좁혀 놓았기 때문이다. 얻는 것은 주문이 들어올 때마다 고치는 고객 인덱스가 둘에서 하나로 주는 것이다(인덱스 하나에 5만 건당 redo 약 12MB). 리프도 3,640 + 3,580 = 7,220블록에서 약 4,350블록으로 준다. 잃는 것은 S2의 1블록이다. 반영은 새 인덱스를 INVISIBLE로 만들어 세션에서 S1 ~ S3을 잰 뒤 VISIBLE로 바꾸고, 옛 인덱스 둘을 INVISIBLE로 돌려 다른 SQL이 견디는지 지켜본 다음 지운다. 조민우 안의 INVISIBLE 인덱스도 지운다.

해설

컬럼 순서의 기준 ④(ORDER BY와 맞추기, 교재 04화 §6)는 선두 등치 조건 바로 뒤에서 힘을 쓴다. 등치로 좁힌 범위가 작으면, 그 뒤에는 정렬을 대신할 컬럼을 먼저 두고 세거나 거르기만 하는 컬럼을 뒤에 둔다.
(CUS_ID, ORD_ST, ORD_DT)가 나은 경우도 있다. 「고객의 미결제 주문을 최근순으로」처럼 상태 등치 조건과 날짜 정렬을 함께 쓰는 SQL이 가장 잦다면 그 순서가 맞다. 화면 빈도로 정한다.
고객이 주문을 수만 건씩 하는 B2B였다면(IDX-020의 2018년 사정) 고객 한 명의 범위가 리프 수백 블록이라 S2에도 상태가 둘째인 인덱스가 필요할 수 있다. "등치로 좁힌 범위가 작다"는 전제가 이 합치기의 근거다.
S1을 FETCH FIRST 20 ROWS ONLY로 써도 같다. 결과 순서는 ORDER BY가 정하고, 인덱스는 그 정렬을 대신할 뿐이다(ACC-025).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황