테이블 하나만 읽는 SQL의 실행계획에 뷰와 해시 조인이 나온다며 FULL 힌트로 막자고 한다

기초SELECT한빛유통117 / 700

상황

정산팀의 「일별 결제 대기 건수」는 달을 고르면 PENDING 주문이 있는 날짜와 건수를 보여 준다. 하루 수십 번 연다. 실행계획을 본 조민우가 이렇게 말했다. "FROM에는 T_ORD_BIG 하나뿐인데 VIEW와 HASH JOIN이 보인다. 통계가 꼬인 것 같으니 FULL 힌트로 고정하자." 아래는 2024년 11월을 골랐을 때다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 2024년 11월 27,390건 · 그중 PENDING 2,190건(6일) · PENDING 전체 80,000건
인덱스 IX_ORD_BIG_DT (ORD_DT): BLEVEL 2 · 리프 2,653블록 · CLUSTERING_FACTOR 약 1,000,000
인덱스 IX_ORD_BIG_ST (ORD_ST): BLEVEL 2 · 리프 2,775블록 · CLUSTERING_FACTOR 17,478 · ORD_ST에 히스토그램
두 인덱스 모두 2018년에 만든 단일 컬럼 인덱스다 · 통계 최신 · 다른 인덱스 없음
다음 달부터 화면에 대기 금액 합계(SUM(ORD_AMT))도 보여 달라는 요청이 와 있다

원본 SQL

sql

SELECT ORD_DT, COUNT(*) AS pend_cnt
  FROM T_ORD_BIG
 WHERE ORD_ST = 'PENDING'
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 GROUP BY ORD_DT
 ORDER BY ORD_DT

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 · 인덱스 규모는 교재 02화와 IDX-005

xplan

------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |      1 |        |      6 |00:00:00.02 |     302 |          |
|   1 |  SORT GROUP BY        |                  |      1 |     30 |      6 |00:00:00.02 |     302 | 2048  (0)|
|*  2 |   FILTER              |                  |      1 |        |   2190 |00:00:00.02 |     302 |          |
|*  3 |    VIEW               | index$_join$_001 |      1 |   2191 |   2190 |00:00:00.02 |     302 |          |
|*  4 |     HASH JOIN         |                  |      1 |        |   2190 |00:00:00.02 |     302 | 1624K (0)|
|*  5 |      INDEX RANGE SCAN | IX_ORD_BIG_DT    |      1 |  27390 |  27390 |00:00:00.01 |      76 |          |
|*  6 |      INDEX RANGE SCAN | IX_ORD_BIG_ST    |      1 |  80000 |  80000 |00:00:00.01 |     226 |          |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - filter(("ORD_ST"='PENDING' AND "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND
              "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1))
   4 - access(ROWID=ROWID)
   5 - access("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)
   6 - access("ORD_ST"='PENDING')

[참고 — 같은 조건 FULL 강제] 5,826

문제

3번 ~ 6번 줄이 무엇을 하는지 설명하시오. FROM에 테이블이 하나뿐인데 VIEW와 HASH JOIN이 나온 이유도 설명하시오.
조민우의 제안(FULL 힌트)을 받아들일지 숫자로 판단하시오.
다음 달 요청대로 SELECT에 SUM(ORD_AMT)를 더하면 실행계획이 어떻게 바뀔지 예상하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고치지 않는다. FULL 힌트도 붙이지 않는다 (인덱스 조인 302 블록 < FULL 5,826 블록)
SELECT ORD_DT, COUNT(*) AS pend_cnt
  FROM T_ORD_BIG
 WHERE ORD_ST = 'PENDING'
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 GROUP BY ORD_DT
 ORDER BY ORD_DT;

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 · 인덱스 규모는 교재 02화와 IDX-005

xplan

[예상 — SELECT 에 SUM(ORD_AMT) 를 더하면]
-----------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |      1 |        |      6 |00:00:00.09 |    5826 |
|   1 |  SORT GROUP BY       |           |      1 |     30 |      6 |00:00:00.09 |    5826 |
|*  2 |   FILTER             |           |      1 |        |   2190 |00:00:00.09 |    5826 |
|*  3 |    TABLE ACCESS FULL | T_ORD_BIG |      1 |   2191 |   2190 |00:00:00.09 |    5826 |
-----------------------------------------------------------------------------------------
   3 - filter(("ORD_ST"='PENDING' AND "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND
              "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1))

[그때의 대안 — IX_ORD_BIG_ST 를 (ORD_ST, ORD_DT, ORD_AMT) 로 바꿔 달면] INDEX RANGE SCAN 하나 · 약 12블록

모범 답안 서술

3번 ~ 6번 줄은 인덱스 조인이다. 5번 줄은 IX_ORD_BIG_DT를 11월 범위로 읽어 (ORD_DT, ROWID) 27,390개를 얻고, 6번 줄은 IX_ORD_BIG_ST를 'PENDING'으로 읽어 (ORD_ST, ROWID) 80,000개를 얻는다. 4번 줄이 두 결과를 ROWID로 해시 조인하면(access(ROWID=ROWID)) 두 조건을 다 만족하는 2,190행만 남는다. SQL에 나오는 컬럼(ORD_DT, ORD_ST)이 두 인덱스에 다 들어 있으므로 테이블에는 가지 않는다. 3번 줄의 index$_join$_001은 옵티마이저가 두 인덱스를 조인하려고 안에서 만든 뷰다. 통계가 꼬인 것이 아니라 옵티마이저가 고른 정상적인 접근 경로다. FULL 힌트는 받아들이지 않는다. 지금 302블록(76 + 226)인 일을 5,826블록으로 바꾸는 힌트이기 때문이다. 인덱스 하나만 쓰는 길도 비싸다. IX_ORD_BIG_DT로 11월 27,390행을 테이블에서 확인하면 CF가 약 1,000,000이라 약 27,466블록이 든다. 다음 달 요청대로 SUM(ORD_AMT)를 더하면 ORD_AMT가 두 인덱스 어디에도 없어서 인덱스 조인을 할 수 없다. 테이블을 읽어야 하므로 FULL 5,826블록으로 바뀔 것이다. 그때 금액까지 싸게 내려면 IX_ORD_BIG_ST를 (ORD_ST, ORD_DT, ORD_AMT)로 바꿔 다는 안을 검토한다. 기존 (ORD_ST)를 앞부분으로 품으므로 인덱스 수가 늘지 않고, 'PENDING이면서 11월'인 2,190항목만 읽어 약 12블록이 된다.

해설

인덱스 조인은 커버링 인덱스가 없을 때 인덱스 둘로 커버링을 대신하는 방식이다. 두 인덱스를 다 읽어야 하므로 각 인덱스가 읽는 범위가 좁을 때 값을 한다. 여기서는 PENDING 80,000항목(226블록)을 읽는 쪽이 일의 4분의 3이다.
실행계획의 VIEW 줄이 SQL에 쓴 뷰라는 보장은 없다. index$_join$, VW_NSO_1, VW_SQ_1처럼 옵티마이저가 변환하면서 만든 뷰는 이름으로 구별된다.
고정하고 싶다면 INDEX_JOIN(t IX_ORD_BIG_DT IX_ORD_BIG_ST)를 쓴다. 다만 지금은 옵티마이저가 스스로 고르고 있으므로 고정할 이유가 없다.
조건 컬럼마다 단일 컬럼 인덱스를 둔 테이블에서는 인덱스 조인 대신 B-tree 인덱스를 비트맵으로 바꿔 AND하는 계획(BITMAP CONVERSION FROM ROWIDS)이 나오기도 한다. 비트맵 인덱스는 ACC 단원의 테이블 구조 세트에서 다룬다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 · 인덱스 규모는 교재 02화와 IDX-005 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 · 인덱스 규모는 교재 02화와 IDX-005 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황