주문 인덱스를 하나 줄이자는 안에 점주 앱이 내림차순 인덱스를 쓰고 있어 못 지운다는 반대가 나왔다

중급DDL버거70 / 700

상황

버거원 DBA 오과장이 분기 인덱스 점검에서 주문 테이블의 ORD_X05를 정리하자고 올렸다. 작년에 개발자가 본사 「매장별 오늘 주문」(S3)의 정렬을 없애겠다며 만든 내림차순 인덱스다. 장애 뒤 재적재 때마다 인덱스 하나만큼 시간과 redo가 더 든다는 것이 이유다. 조민우는 반대했다. "V$SQL_PLAN을 보니 점주 앱 「최근 주문 20건」(S1)이 하루 4만 번 X05를 씁니다. 지우면 점주 앱이 매번 정렬을 하게 됩니다." 박팀장은 둘 중 누구 말이 맞는지 숫자로 판정해 오라고 했다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · 주문 일시 순으로 쌓였다(하루치가 약 31블록 안에 모여 있다) · ORD_NO · ORD_DT VARCHAR2(8) · ORD_HMS VARCHAR2(6) · SHOP_NO VARCHAR2(10) 200곳 · 하루 약 1,370건, 매장 하나의 하루 주문은 평균 7건
인덱스(모두 BLEVEL 2 · 통계 최신)
- ORD_PK (ORD_NO)
- ORD_X01 (ORD_DT, ORD_HMS): 리프 3,774블록
- ORD_X02 (SHOP_NO, ORD_DT, ORD_HMS): 점주 앱용 · 리프 약 4,050블록
- ORD_X05 (SHOP_NO, ORD_DT DESC, ORD_HMS DESC): 작년에 S3용으로 만든 내림차순 인덱스 · 리프 약 4,050블록 · CF도 ORD_X02와 같다
POS가 주문마다 INSERT한다(하루 약 1,370건) · 장애 뒤 재적재 때는 100만 건을 다시 넣는다(인덱스 하나에 5만 건당 redo 약 12MB, 교재 14화)

원본 SQL

sql

-- S1 점주 앱 「최근 주문 20건」 (하루 약 4만 번)
SELECT ORD_NO, ORD_DT, ORD_HMS
  FROM ORD
 WHERE SHOP_NO = :v_shop
 ORDER BY ORD_DT DESC, ORD_HMS DESC
 FETCH FIRST 20 ROWS ONLY;

-- S2 점주 앱 「기간 주문 목록」 (하루 수천 번)
SELECT ORD_NO, ORD_DT, ORD_HMS
  FROM ORD
 WHERE SHOP_NO = :v_shop
   AND ORD_DT BETWEEN :v_from AND :v_to
 ORDER BY ORD_DT, ORD_HMS;

-- S3 본사 「매장별 오늘 주문」 (본사 PC 10대가 5분마다)
SELECT SHOP_NO, ORD_HMS, ORD_NO
  FROM ORD
 WHERE ORD_DT = :v_dt
 ORDER BY SHOP_NO, ORD_HMS DESC;

-- 작년에 S3 의 정렬을 없애려고 만든 인덱스
CREATE INDEX ORD_X05 ON ORD (SHOP_NO, ORD_DT DESC, ORD_HMS DESC);

실행계획 학습용 예시

xplan

[S1 · 지금 · SH0042]
----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |      1 |        |     20 |00:00:00.01 |      24 |
|*  1 |  VIEW                          |         |      1 |     20 |     20 |00:00:00.01 |      24 |
|*  2 |   WINDOW NOSORT STOPKEY        |         |      1 |     20 |     20 |00:00:00.01 |      24 |
|   3 |    TABLE ACCESS BY INDEX ROWID | ORD     |      1 |     20 |     20 |00:00:00.01 |      24 |
|*  4 |     INDEX RANGE SCAN           | ORD_X05 |      1 |     20 |     20 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORD_DT") DESC ,INTERNAL_FUNCTION("ORD_HMS") DESC )<=20)
   4 - access("SHOP_NO"=:V_SHOP)

[S3 · 지금 · 2013-03-15]
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |      1 |        |   1370 |00:00:00.01 |      38 |          |
|   1 |  SORT ORDER BY                        |         |      1 |   1370 |   1370 |00:00:00.01 |      38 |      83K |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | ORD     |      1 |   1370 |   1370 |00:00:00.01 |      38 |          |
|*  3 |    INDEX RANGE SCAN                   | ORD_X01 |      1 |   1370 |   1370 |00:00:00.01 |       7 |          |
----------------------------------------------------------------------------------------------------------------------
   3 - access("ORD_DT"=:V_DT)

[S3 · 같은 날 · INDEX_SS(ORD ORD_X05) 힌트로 잰 것]
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |   1370 |00:00:00.03 |    1968 |
|   1 |  TABLE ACCESS BY INDEX ROWID | ORD     |      1 |   1370 |   1370 |00:00:00.03 |    1968 |
|*  2 |   INDEX SKIP SCAN            | ORD_X05 |      1 |   1370 |   1370 |00:00:00.01 |     598 |
--------------------------------------------------------------------------------------------------
   2 - access(SYS_OP_DESCEND("ORD_DT")=SYS_OP_DESCEND(:V_DT))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("ORD_DT"))=:V_DT)

[V$SQL_PLAN · 지난 2주]  ORD_X05를 쓰는 SQL은 S1 하나다 · S2는 ORD_X02를 쓰고, S3는 늘 ORD_X01과 SORT ORDER BY다

문제

조민우의 반대(ORD_X05를 지우면 S1이 정렬을 한다)가 맞는지 S1의 조건과 ORD_X02의 컬럼 순서로 판정하시오.
ORD_X05를 만든 본래 목적(S3의 정렬 없애기)이 맞는 판단이었는지 두 계획의 숫자로 설명하시오.
ORD_X05를 정리하는 DDL을 작성하고, 지우기 전에 확인할 것과 지워서 얻는 것을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- S1 : 선두 SHOP_NO 가 = 로 고정되므로 ORD_X02 를 거꾸로 읽어 같은 순서를 얻는다 (정렬 없음 · 24 블록 그대로)
-- S3 : 지금처럼 ORD_X01 로 그날 1,370 행을 읽고 메모리에서 정렬한다 (38 블록)
ALTER INDEX ORD_X05 INVISIBLE;   -- S1 이 ORD_X02 DESCENDING 으로 바뀌고 Buffers 가 그대로인지 보고, 2주 동안 다른 SQL 을 지켜본다
DROP INDEX ORD_X05;
-- S1 · S2 · S3 의 SQL 은 그대로

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

xplan

[S1 · ORD_X05 를 INVISIBLE 로 돌린 뒤 · SH0042]
-----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |         |      1 |        |     20 |00:00:00.01 |      24 |
|*  1 |  VIEW                           |         |      1 |     20 |     20 |00:00:00.01 |      24 |
|*  2 |   WINDOW NOSORT STOPKEY         |         |      1 |     20 |     20 |00:00:00.01 |      24 |
|   3 |    TABLE ACCESS BY INDEX ROWID  | ORD     |      1 |     20 |     20 |00:00:00.01 |      24 |
|*  4 |     INDEX RANGE SCAN DESCENDING | ORD_X02 |      1 |     20 |     20 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORD_DT") DESC ,INTERNAL_FUNCTION("ORD_HMS") DESC )<=20)
   4 - access("SHOP_NO"=:V_SHOP)

모범 답안 서술

조민우의 반대는 맞지 않다. S1은 SHOP_NO = :v_shop으로 선두 컬럼을 한 값에 고정한다. ORD_X02 (SHOP_NO, ORD_DT, ORD_HMS)에서 한 매장의 항목은 한곳에 모여 날짜와 시각의 오름차순으로 놓여 있으므로, 그 구간을 거꾸로 읽으면(INDEX RANGE SCAN DESCENDING) 그대로 ORD_DT DESC, ORD_HMS DESC 순서가 된다. 그래서 정렬 없이 20건에서 멈추고 Buffers도 24로 같다. 지금 S1이 ORD_X05를 쓰는 것은 두 인덱스의 크기와 CF가 같아 비용이 같고, 옵티마이저가 그중 하나를 골랐을 뿐이다. 선두 컬럼이 등치 조건으로 고정되면 뒤 컬럼의 방향은 인덱스를 어느 쪽으로 읽느냐로 해결되므로 내림차순 컬럼을 따로 둘 이유가 없다. S2도 ORD_X02를 앞으로 읽어 끝난다. 내림차순 컬럼이 쓸모 있는 것은 선두 컬럼이 여러 값에 걸쳐 있으면서 방향을 섞어 정렬할 때다. S3가 그런 모양이다. 매장 순(오름)과 시각 역순(내림)을 섞어 달라는데 SHOP_NO 조건이 없다. 그런데 ORD_X05의 순서대로 그날 주문을 읽으려면 매장 200곳마다 들어가는 SKIP SCAN을 해야 하고(인덱스 598블록), 그 순서를 지키느라 행마다 테이블을 찾아가 1,370번을 더 읽는다(Buffers 1,968). 지금처럼 ORD_X01로 그날 1,370행을 모아 메모리에서 정렬하면 38블록이고 정렬에 쓰는 메모리는 83KB 남짓이다. 그래서 옵티마이저는 S3에 ORD_X05를 한 번도 쓰지 않았다. 정렬을 없애려고 만든 인덱스가 정렬보다 50배 넘게 비싸서 본래 목적도 성립하지 않는다. 정리는 ORD_X05를 INVISIBLE로 돌려 S1이 ORD_X02 DESCENDING으로 바뀌고 Buffers가 24 그대로인지 확인하는 데서 시작한다. 그 뒤 2주쯤 V$SQL_PLAN과 DBA_INDEX_USAGE로 다른 SQL이 쓰지 않는지, 힌트로 ORD_X05를 부르는 SQL이 없는지 보고 지운다. 얻는 것은 주문을 넣을 때마다 고칠 인덱스가 하나 줄어드는 것(재적재 100만 건이면 redo 약 240MB)과 리프 약 4,050블록(약 32MB)이다. 컬럼에 DESC를 붙인 인덱스는 SYS_OP_DESCEND를 쓰는 함수 기반 인덱스라, 이름만 봐서는 다른 인덱스와 같은 일을 하는지 알아보기 어렵다. 점검표에서 접두 중복만 보던 규칙에 걸리지 않은 것도 그래서다.

해설

판정 규칙: (A, B DESC)처럼 방향을 섞은 인덱스가 필요한 것은 ORDER BY가 A의 여러 값에 걸쳐 방향을 섞을 때뿐이다. A가 등치 조건으로 한 값이면 (A, B)를 거꾸로 읽어 같은 순서가 나온다. 모든 컬럼이 DESC인 인덱스는 언제나 오름차순 인덱스를 거꾸로 읽는 것과 같다.
방향을 섞어야 하는 정렬이라도 정렬할 행이 적으면 정렬이 싸다. SORT-012처럼 넓은 날짜 범위를 (내림, 오름)으로 보며 첫 10건만 자르는 화면이라야 인덱스가 정렬을 대신할 값어치가 있다.
힌트가 가리키는 인덱스가 사라지면 힌트는 에러 없이 무시된다. 지우기 전에 V$SQL의 SQL 글자에서 ORD_X05를 찾아 두고, 있으면 힌트부터 고친다(SORT-014의 이름을 바꾼 인덱스와 같은 함정).
점주 앱 셋을 ORD_X02 하나로 받치는 설계는 ACC-073에서 정했다. 같은 컬럼을 방향만 바꿔 하나 더 두면 받치는 SQL은 늘지 않고 적재 비용만 는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황