1분마다 새로 고치는 「오늘 주문 건수」 타일이 건수 하나에 1,376블록을 읽는다

기초SELECT버거121 / 700

상황

버거원 본사 상황판의 「오늘 주문 건수」 타일은 1분마다 새로 고친다. 지난달 코드 리뷰에서 한 개발자가 COUNT(*)를 COUNT(SHOP_NO)로 바꿨다. 리뷰 메모는 이렇다. "별표는 모든 컬럼을 읽으니 컬럼 하나만 세는 것이 빠르다." 그 뒤로 이 타일 SQL이 AWR의 'SQL ordered by Gets' 상위에 올라왔다. 아래는 2013년 12월 24일을 골랐을 때다.

테이블과 인덱스

ORD_RANDOM(운영본): 1,000,000행 · 22,414블록 · 무작위 순서로 적재 · ORD_DT VARCHAR2(8) · 하루 약 1,370건(2013-12-24 1,368건)
SHOP_NO VARCHAR2(10)는 NULL 허용으로 선언되어 있다(지금 NULL은 0건)
인덱스 ORD_RANDOM_PK (ORD_NO) · ORD_RANDOM_X01 (ORD_DT, ORD_HMS): BLEVEL 2 · 리프 3,774블록 · CLUSTERING_FACTOR 999,958 · 통계 최신

원본 SQL

sql

SELECT COUNT(SHOP_NO) AS ord_cnt
  FROM ORD_RANDOM
 WHERE ORD_DT = :v_dt

실행계획 학습용 예시 · CF는 교재 04화 실측

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      1 |00:00:00.02 |    1376 |
|   1 |  SORT AGGREGATE                      |                |      1 |      1 |      1 |00:00:00.02 |    1376 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD_RANDOM     |      1 |   1370 |   1368 |00:00:00.02 |    1376 |
|*  3 |    INDEX RANGE SCAN                  | ORD_RANDOM_X01 |      1 |   1370 |   1368 |00:00:00.01 |       8 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ORD_DT"=:V_DT)

문제

3번 줄에서 인덱스를 8블록만 읽고도 2번 줄에서 1,368블록을 더 읽는 이유를 설명하시오. 리뷰 메모가 왜 틀렸는지도 설명하시오.
같은 건수를 인덱스만으로 세도록 SQL을 고치시오. COUNT(SHOP_NO)와 COUNT(*)는 뜻도 다를 수 있다는 점을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT COUNT(*) AS ord_cnt          -- 행을 센다 · 조건 컬럼 ORD_DT 만 있으면 되므로 X01 만 읽는다
  FROM ORD_RANDOM
 WHERE ORD_DT = :v_dt;

개선 후 실행계획 학습용 예시 · CF는 교재 04화 실측

xplan

------------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |      1 |        |      1 |00:00:00.01 |       8 |
|   1 |  SORT AGGREGATE   |                |      1 |      1 |      1 |00:00:00.01 |       8 |
|*  2 |   INDEX RANGE SCAN| ORD_RANDOM_X01 |      1 |   1370 |   1368 |00:00:00.01 |       8 |
------------------------------------------------------------------------------------------
   2 - access("ORD_DT"=:V_DT)

모범 답안 서술

COUNT(SHOP_NO)는 'SHOP_NO 값이 NULL이 아닌 행'을 센다. 그러려면 행마다 SHOP_NO 값을 봐야 하는데, SHOP_NO는 X01에 없다. 그래서 3번 줄에서 그날의 1,368항목을 8블록에 찾은 뒤, 2번 줄에서 행마다 테이블 블록을 찾아간다. X01의 CF가 999,958로 행 수와 같아서 1,368행이 거의 모두 다른 블록에 있고, 1,368블록을 더 읽어 합계 1,376블록이 된다. COUNT(*)의 별표는 '모든 컬럼'이 아니라 '행'을 뜻한다. 조건을 만족하는 행이 몇 개인지만 세므로 어떤 컬럼 값도 읽을 필요가 없다. 조건 컬럼 ORD_DT가 X01에 있으므로 인덱스 항목 수만 세면 된다. 리뷰 메모는 별표를 SELECT *의 별표처럼 읽은 오해다. COUNT(*)로 되돌리면 2번 줄이 사라지고 1,376 → 8블록이 된다. COUNT(1)이나 COUNT(ORD_DT)도 같다(ORD_DT는 조건 때문에 NULL일 수 없다). 뜻도 다를 수 있다. SHOP_NO는 NULL 허용이므로, 나중에 매장 번호 없이 들어오는 주문(예: 본사 직판)이 생기면 COUNT(SHOP_NO)는 그 주문을 조용히 빼고 센다. 지금은 NULL이 0건이라 숫자가 같을 뿐이다. 타일이 보여 주려는 것은 '오늘 주문 건수'이므로 COUNT(*)가 뜻으로도 맞다.

해설

컬럼이 NOT NULL로 선언되어 있으면 옵티마이저는 COUNT(그 컬럼)을 COUNT(*)와 같은 것으로 보고 같은 계획을 쓴다. SHOP_NO에 NOT NULL을 선언해도 블록 문제는 풀린다. 하지만 세려는 것이 행이라면 처음부터 COUNT(*)로 쓰는 것이 맞다.
COUNT(*)가 테이블로 가는 것은 조건 컬럼이 인덱스에 없을 때다. 그때는 세는 방법이 아니라 조건과 인덱스의 관계를 본다.
같은 오해가 COUNT(PK 컬럼)으로 바꾸는 습관으로도 나타난다. PK는 NOT NULL이라 결과와 계획이 COUNT(*)와 같으므로 해는 없지만, 얻는 것도 없다.
보관본 ORD(CF 22,223)였다면 같은 실수로 약 30블록만 더 읽어 눈에 띄지 않았을 것이다. CF가 나쁜 테이블에서 작은 실수가 크게 드러난다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · CF는 교재 04화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · CF는 교재 04화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황