본사 상황판의 「방금 들어온 주문 20건」이 10초마다 22,422블록을 읽는다

기초SELECT버거312 / 700

상황

본사 운영실 상황판은 전 매장의 가장 최근 주문 20건을 보여 주고 10초마다 새로 고친다. 운영실 대형 모니터 4대와 지역 담당 PC 20대가 이 화면을 띄워 두어 하루 약 20만 번 호출된다. DBA가 메시지를 보냈다. "논리 읽기 1위가 이 SQL이에요. ORD_X01 (ORD_DT, ORD_HMS) 인덱스가 있는데 왜 FULL로 읽죠?" 개발자는 일자와 시각이 따로 있으니 둘을 붙여야 정확한 시각순이 된다고 생각해 정렬 키를 ORD_DT || ORD_HMS로 썼다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · 날짜순으로 적재되어 최근 주문은 테이블 끝 블록에 모여 있다 · ORD_NO PK · SHOP_NO 매장 200곳
ORD_DT VARCHAR2(8) '20131231' · ORD_HMS VARCHAR2(6) '235958' · 두 컬럼 모두 NOT NULL이고 늘 8자리, 6자리다(0 채움 정상)
인덱스 ORD_PK (ORD_NO), ORD_X01 (ORD_DT, ORD_HMS): BLEVEL 2 · 리프 약 3,500블록 · CF 22,223 · 통계 최신

원본 SQL

sql

SELECT ORD_NO, SHOP_NO, ORD_DT, ORD_HMS
  FROM (SELECT o.ORD_NO, o.SHOP_NO, o.ORD_DT, o.ORD_HMS
          FROM ORD o
         ORDER BY o.ORD_DT || o.ORD_HMS DESC)
 WHERE ROWNUM <= 20

실행계획 학습용 예시

xplan

--------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |        |     20 |00:00:00.27 |   22422 |          |
|*  1 |  COUNT STOPKEY          |      |      1 |        |     20 |00:00:00.27 |   22422 |          |
|   2 |   VIEW                  |      |      1 |   1000K|     20 |00:00:00.27 |   22422 |          |
|*  3 |    SORT ORDER BY STOPKEY|      |      1 |   1000K|     20 |00:00:00.27 |   22422 | 4096  (0)|
|   4 |     TABLE ACCESS FULL   | ORD  |      1 |   1000K|   1000K|00:00:00.08 |   22422 |          |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=20)
   3 - filter(ROWNUM<=20)

문제

ORD_X01이 있는데도 4번 줄이 테이블 전체를 읽고 3번 줄이 정렬하는 이유를 설명하시오.
결과가 바뀌지 않게 SQL을 고쳐 인덱스를 거꾸로 읽다가 20건에서 멈추게 하시오. 고친 정렬이 원래 정렬과 같은 순서라고 말할 수 있는 근거도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 정렬 키를 가공하지 않는다 : ORD_X01 의 컬럼 순서 · 방향 그대로 (둘 다 DESC → 인덱스를 거꾸로)
SELECT ORD_NO, SHOP_NO, ORD_DT, ORD_HMS
  FROM (SELECT o.ORD_NO, o.SHOP_NO, o.ORD_DT, o.ORD_HMS
          FROM ORD o
         ORDER BY o.ORD_DT DESC, o.ORD_HMS DESC)
 WHERE ROWNUM <= 20

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

xplan

------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |     20 |00:00:00.01 |       5 |
|*  1 |  COUNT STOPKEY                |         |      1 |        |     20 |00:00:00.01 |       5 |
|   2 |   VIEW                        |         |      1 |     20 |     20 |00:00:00.01 |       5 |
|   3 |    TABLE ACCESS BY INDEX ROWID| ORD     |      1 |   1000K|     20 |00:00:00.01 |       5 |
|   4 |     INDEX FULL SCAN DESCENDING| ORD_X01 |      1 |     20 |     20 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=20)

모범 답안 서술

3번 줄의 정렬 키는 ORD_DT || ORD_HMS라는 새 문자열이다. 인덱스 ORD_X01에는 ORD_DT와 ORD_HMS가 따로 줄 서 있을 뿐, 붙인 문자열은 인덱스 어디에도 없다. 그래서 옵티마이저는 인덱스 순서로 정렬을 대신할 수 없다고 보고, 4번 줄에서 100만 행(22,422블록)을 다 읽은 뒤 3번 줄에서 Top-N 정렬(SORT ORDER BY STOPKEY)로 20건을 남긴다. WHERE 절 컬럼을 가공하면 범위를 못 잡듯이, ORDER BY 컬럼을 가공하면 인덱스의 순서를 쓰지 못한다. 정렬 키를 ORDER BY ORD_DT DESC, ORD_HMS DESC로 바꾸면 인덱스의 컬럼 순서, 방향과 같아진다. 결과는 바뀌지 않는다. 두 컬럼이 늘 8자리, 6자리로 고정 길이이고 NOT NULL이라서, 붙인 문자열의 내림차순은 '앞 8자리 내림차순, 같으면 뒤 6자리 내림차순'과 같은 순서이기 때문이다. 고친 SQL은 ORD_X01을 거꾸로(INDEX FULL SCAN DESCENDING) 읽다가 20건에서 멈춘다(COUNT STOPKEY). 정렬 줄과 work area가 사라지고 Buffers는 22,422 → 5로 준다(인덱스 3, 테이블 2). 최근 주문은 테이블 끝 블록에 모여 있어 테이블 방문도 두 블록이면 된다. 하루 약 20만 번 호출되므로 하루 약 45억 블록이 약 100만 블록이 된다.

해설

NOT NULL이 필요하다: WHERE 조건이 없는 정렬을 인덱스가 대신하려면 모든 행이 인덱스에 있어야 한다. ORD_DT와 ORD_HMS가 둘 다 NULL 허용이었다면 옵티마이저는 이 인덱스로 정렬을 대신하지 않는다(교재 09화 rev01의 세 조건).
길이가 고정이 아니면 다르다: 유통의 SLE_HMS처럼 앞자리 0이 빠진 값이 섞여 있으면 문자열 정렬 자체가 틀린다. 그때는 SQL보다 데이터를 먼저 고친다(교재 03화, 09화 §12).
같은 초에 들어온 주문은 둘 사이 순서가 정해지지 않는다. 상황판처럼 페이지를 넘기지 않는 화면이면 괜찮지만, 페이징을 붙이려면 정렬 끝에 ORD_NO DESC를 붙이고 인덱스에도 넣는다(SORT-005).
FETCH FIRST 20 ROWS ONLY로 써도 같은 일을 한다. 실행계획의 이름이 WINDOW NOSORT STOPKEY로 바뀔 뿐이다.
주문번호가 시각 순으로 매겨진다는 전제가 확인되면 ORDER BY ORD_NO DESC로 PK를 거꾸로 읽어도 된다. 그 전제는 대장에 적고 채번 방식이 바뀌면 다시 확인한다(교재 09화 §12의 SLE_NO).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황