상품 상세의 상품평 50건을 띄우는 데 상품평 테이블을 거의 통째로 읽는다

기초DDL한빛유통124 / 700

상황

쇼핑몰 상품 상세 화면 아래의 「상품평」 탭은 그 상품의 평가 50건을 최신 순으로 모두 보여 준다. 상세 화면은 하루 약 12만 번 열린다. 상품평 테이블은 48블록짜리 작은 테이블인데, AWR의 'SQL ordered by Gets'에서 이 SQL이 5위에 올라 있다. 조민우가 PK를 타라고 INDEX_DESC 힌트를 넣어 재 보니 오히려 한 블록을 더 읽었다. 아래는 상품 ITM00007이다.

테이블과 인덱스

T_ITM_EVL 상품평가: 5,000행 · 48블록(블록당 약 104행) · 컬럼 ITM_ID · EVL_LST_NO · CUS_ID · EVL_DSC(짧은 한 줄 평) · EVL_DT · EVL_PT
상품 100종에 50건씩 · 평가 번호(EVL_LST_NO) 순으로 쌓였고, 평가 번호가 상품 100종을 하나씩 돌아가며 매겨졌다 · 그래서 상품 하나의 50건은 100행 간격으로 떨어져 48블록 가운데 47블록에 하나씩 있다
PK_T_ITM_EVL (ITM_ID, EVL_LST_NO): BLEVEL 1 · 리프 15블록 · CLUSTERING_FACTOR 4,700 · 다른 인덱스는 없다 · 통계 최신
새 평가는 하루 수십 건 들어오고, 고치거나 지우는 일은 드물다 · 이 테이블은 상품평 탭과 밤마다 도는 「상품별 평균 평점」 집계가 읽는다

원본 SQL

sql

SELECT EVL_LST_NO, CUS_ID, EVL_DT, EVL_PT, EVL_DSC
  FROM T_ITM_EVL
 WHERE ITM_ID = :v_itm
 ORDER BY EVL_LST_NO DESC

실행계획 학습용 예시 · 행이 흩어진 모양은 적재 스크립트 그대로

xplan

[옵티마이저가 고른 계획]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |     50 |00:00:00.01 |      48 |
|   1 |  SORT ORDER BY                |              |      1 |     50 |     50 |00:00:00.01 |      48 |
|*  2 |   TABLE ACCESS FULL           | T_ITM_EVL    |      1 |     50 |     50 |00:00:00.01 |      48 |
-------------------------------------------------------------------------------------------------------
   2 - filter("ITM_ID"=:V_ITM)

[조민우가 /*+ INDEX_DESC(T_ITM_EVL PK_T_ITM_EVL) */ 로 재 본 계획]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |     50 |00:00:00.01 |      49 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | T_ITM_EVL    |      1 |     50 |     50 |00:00:00.01 |      49 |
|*  2 |   INDEX RANGE SCAN DESCENDING | PK_T_ITM_EVL |      1 |     50 |     50 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ITM_ID"=:V_ITM)

문제

50건을 보여 주는 데 FULL로는 48블록, PK로는 49블록을 읽는 이유를 행이 쌓인 모양과 CLUSTERING_FACTOR로 설명하시오.
SQL은 그대로 두고 테이블 구조를 바꿔 50건이 몇 블록으로 끝나게 하시오. DDL을 작성하고, 모든 컬럼을 담은 인덱스를 하나 더 만드는 방법과 비교하여 그 구조의 대가를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 상품평을 PK (ITM_ID, EVL_LST_NO) 순서로 리프에 담는다 → 한 상품의 50건이 한두 블록에 모인다
CREATE TABLE T_ITM_EVL_IOT
( ITM_ID, EVL_LST_NO, CUS_ID, EVL_DSC, EVL_DT, EVL_PT,
  CONSTRAINT PK_T_ITM_EVL_IOT PRIMARY KEY (ITM_ID, EVL_LST_NO) )
ORGANIZATION INDEX
AS
SELECT ITM_ID, EVL_LST_NO, CUS_ID, EVL_DSC, EVL_DT, EVL_PT
  FROM T_ITM_EVL;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ITM_EVL_IOT');

-- 점검 시간에 바꿔 단다 (권한과 동의어도 옮긴다)
ALTER TABLE T_ITM_EVL     RENAME TO T_ITM_EVL_OLD;
ALTER TABLE T_ITM_EVL_IOT RENAME TO T_ITM_EVL;

SELECT EVL_LST_NO, CUS_ID, EVL_DT, EVL_PT, EVL_DSC     -- 화면 SQL 은 그대로
  FROM T_ITM_EVL
 WHERE ITM_ID = :v_itm
 ORDER BY EVL_LST_NO DESC;

개선 후 실행계획 학습용 예시 · 행이 흩어진 모양은 적재 스크립트 그대로

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |      1 |        |     50 |00:00:00.01 |       3 |
|*  1 |  INDEX RANGE SCAN DESCENDING  | PK_T_ITM_EVL_IOT |      1 |     50 |     50 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------
   1 - access("ITM_ID"=:V_ITM)

모범 답안 서술

첫 계획은 테이블 48블록을 다 읽고 50건을 남긴 뒤 정렬한다. 둘째 계획은 PK로 50건의 ROWID를 찾지만(인덱스 2블록), 테이블에서 47블록을 읽어 합계 49블록이 된다. 같은 50건이라도 행이 어디에 있느냐가 읽는 양을 정한다. T_ITM_EVL은 평가 번호 순서로 쌓였고, 평가 번호가 상품 100종을 하나씩 돌아가며 매겨졌다. 그래서 상품 하나의 평가는 100행 간격으로 떨어져 있고, 블록 하나에 약 104행이 들어가므로 50건이 47블록에 하나씩 흩어진다. PK의 CLUSTERING_FACTOR 4,700이 행 수 5,000에 가까운 것이 그 뜻이다. 인덱스를 타든 FULL로 읽든 테이블 거의 전부를 읽을 수밖에 없다. 테이블을 IOT(ORGANIZATION INDEX)로 다시 만들면 행 전체가 PK (ITM_ID, EVL_LST_NO) 순서로 인덱스 리프 블록에 저장된다. 한 상품의 50건은 리프 한두 블록에 모이고, ROWID로 테이블을 찾아가는 단계가 없다. 상품평 탭은 루트와 리프만 읽고 끝나며 PK를 거꾸로 읽으므로 정렬도 필요 없다(Buffers 48 → 3). 하루 12만 번이면 약 576만 → 36만 블록이다. 모든 컬럼을 담은 인덱스를 하나 더 만들어도 테이블 액세스는 없어진다. 하지만 같은 데이터를 테이블과 인덱스에 두 벌 두고, 평가가 들어올 때마다 둘 다 고친다. IOT는 PK 인덱스가 곧 테이블이라 데이터를 한 벌만 둔다. 대가도 있다. 새 평가는 상품마다 그 상품 구간의 끝, 즉 IOT의 가운데에 들어가므로 리프 블록 분할이 생긴다. 하루 수십 건이라 이 테이블에서는 문제가 되지 않는다. 다른 컬럼으로 찾는 보조 인덱스를 만들면 ROWID 대신 PK와 추측 주소(physical guess)로 된 논리 ROWID를 쓴다. 기존 테이블을 IOT로 바꾸는 명령은 없으므로 새로 만들어 이름을 바꾸거나 DBMS_REDEFINITION으로 온라인 재정의를 한다.

해설

함정: 힌트로 PK를 고정해도 줄지 않는다. 읽는 양을 정하는 것은 접근 경로가 아니라 행이 흩어진 모양(CF)이다. 이 테이블에서는 FULL이 오히려 한 블록 적다.
테이블을 상품 순으로 정렬해 다시 쌓아도(CTAS … ORDER BY) 지금은 50건이 한두 블록에 모인다. 하지만 새 평가는 테이블 끝에 쌓이므로 시간이 지나면 다시 흩어진다. IOT는 넣을 때마다 PK 자리에 넣으므로 순서가 유지된다(교재 04화 '고쳐도 영구하지 않다').
IOT가 맞는 테이블은 폭이 좁고, 거의 모든 조회가 PK 앞부분으로 범위를 읽으며, 입력 순서와 조회 순서가 다른 테이블이다. 긴 컬럼이 있으면 리프 한 블록에 담기는 행이 줄어 이점이 작아진다(ACC-050).
밤마다 도는 「상품별 평균 평점」 집계는 IOT 전체를 INDEX FAST FULL SCAN으로 읽는다. 리프 수가 테이블 블록 수와 비슷하므로 비용은 거의 같다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 행이 흩어진 모양은 적재 스크립트 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 행이 흩어진 모양은 적재 스크립트 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황