상품평 본문을 받기 시작한 뒤로 긴 상품평은 등록이 실패하고, 상품평 탭은 두 배 넘게 읽는다

중급INSERT한빛유통125 / 700

상황

지난달 상품평 테이블 T_ITM_EVL을 IOT로 바꿔 상품평 탭이 3블록으로 끝나게 됐다(ACC-049). 이번 달에는 짧은 한 줄 평만 받던 EVL_DSC를 4,000바이트로 넓혀 본문을 받기 시작했고, 옛 상품평의 본문도 원본 시스템에서 옮겨 채웠다. 화면을 바꾸면서 상품평 탭 목록에서는 본문을 빼고, 한 건을 누르면 팝업으로 본문을 보여 준다. 그 뒤로 고객센터에 "긴 리뷰를 쓰면 등록 버튼을 눌러도 오류가 난다"는 문의가 하루 몇 건씩 들어오고, 본문을 읽지도 않는 상품평 탭은 3블록에서 8블록으로 늘었다.

테이블과 인덱스

T_ITM_EVL 상품평가(IOT): 5,000행 · PK PK_T_ITM_EVL_IOT (ITM_ID, EVL_LST_NO) · BLEVEL 1 · 리프 33블록 → 본문을 채운 뒤 약 690블록 · OVERFLOW 세그먼트 없음 · PCTTHRESHOLD 기본값(50)
컬럼 순서: ITM_ID · EVL_LST_NO · CUS_ID · EVL_DSC · EVL_DT · EVL_PT
본문 길이: 평균 약 600바이트(한글 200자 남짓) · 4,000바이트 가까운 긴 본문이 가끔 들어온다 · 본문 말고 한 행은 약 60바이트
상품 하나에 50건 · 하루 새 평가 수십 건

원본 SQL

sql

-- 상품평 탭 목록 (본문은 빼고)
SELECT EVL_LST_NO, CUS_ID, EVL_DT, EVL_PT
  FROM T_ITM_EVL
 WHERE ITM_ID = :v_itm
 ORDER BY EVL_LST_NO DESC;

-- 본문 팝업
SELECT EVL_DSC FROM T_ITM_EVL WHERE ITM_ID = :v_itm AND EVL_LST_NO = :v_no;

-- 상품평 등록
INSERT INTO T_ITM_EVL (ITM_ID, EVL_LST_NO, CUS_ID, EVL_DSC, EVL_DT, EVL_PT)
VALUES (:v_itm, :v_no, :v_cus, :v_dsc, SYSDATE, :v_pt);

-- 이번 달에 한 변경
ALTER TABLE T_ITM_EVL MODIFY (EVL_DSC VARCHAR2(4000));

실행계획 학습용 예시 · 본문 길이 분포와 리프 수는 어림값

xplan

[상품평 탭 목록 · 본문을 채운 뒤]
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |      1 |        |     50 |00:00:00.01 |       8 |
|*  1 |  INDEX RANGE SCAN DESCENDING  | PK_T_ITM_EVL_IOT |      1 |     50 |     50 |00:00:00.01 |       8 |
-----------------------------------------------------------------------------------------------------------
   1 - access("ITM_ID"=:V_ITM)

[등록 실패 로그 · 오늘 3건 가운데 하나]
INSERT INTO T_ITM_EVL (ITM_ID, EVL_LST_NO, CUS_ID, EVL_DSC, EVL_DT, EVL_PT) VALUES (:1, :2, :3, :4, SYSDATE, :5)
  ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces
  (:4 의 길이 3,962바이트)

문제

긴 상품평의 등록이 ORA-01429로 실패하는 이유와, 본문을 읽지 않는 상품평 탭이 3블록에서 8블록으로 늘어난 이유를 IOT의 리프 블록에 무엇이 담기는지로 설명하시오.
IOT를 유지하면서 두 문제를 함께 고치는 DDL을 작성하시오. 고친 뒤 상품평 탭과 본문 팝업이 각각 무엇을 읽는지와 그 구조의 대가를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 본문(EVL_DSC)은 OVERFLOW 세그먼트로 · 목록이 읽는 컬럼(EVL_PT 까지)만 리프에 남긴다
-- INCLUDING 은 컬럼 순서로 자르므로 EVL_DSC 를 맨 뒤로 옮겨 새로 만든다
CREATE TABLE T_ITM_EVL_NEW
( ITM_ID, EVL_LST_NO, CUS_ID, EVL_DT, EVL_PT, EVL_DSC,
  CONSTRAINT PK_T_ITM_EVL_NEW PRIMARY KEY (ITM_ID, EVL_LST_NO) )
ORGANIZATION INDEX
PCTTHRESHOLD 20
INCLUDING EVL_PT
OVERFLOW
AS
SELECT ITM_ID, EVL_LST_NO, CUS_ID, EVL_DT, EVL_PT, EVL_DSC
  FROM T_ITM_EVL;

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

ALTER TABLE T_ITM_EVL     RENAME TO T_ITM_EVL_OLD;
ALTER TABLE T_ITM_EVL_NEW RENAME TO T_ITM_EVL;
-- 목록 · 팝업 · 등록 SQL 은 그대로 (INSERT 에 컬럼 목록이 있어 컬럼 순서가 바뀌어도 된다)

개선 후 실행계획 학습용 예시 · 본문 길이 분포와 리프 수는 어림값

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_NEW |      1 |     50 |     50 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------
   1 - access("ITM_ID"=:V_ITM)

[본문 팝업 · 루트 1 + 리프 1 + overflow 1]
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |      1 |        |      1 |00:00:00.01 |       3 |
|*  1 |  INDEX UNIQUE SCAN            | PK_T_ITM_EVL_NEW |      1 |      1 |      1 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------
   1 - access("ITM_ID"=:V_ITM AND "EVL_LST_NO"=:V_NO)

모범 답안 서술

IOT는 PK 인덱스의 리프 블록에 행 전체를 담는다. 행 하나가 블록에서 차지할 수 있는 크기에는 PCTTHRESHOLD(기본 50%, 8KB 블록이면 약 4KB)라는 한도가 있고, 한도를 넘는 부분을 둘 OVERFLOW 세그먼트가 없으면 그 행은 넣을 수 없다. 본문이 4,000바이트 가까운 상품평이 ORA-01429로 거부되는 이유다. 상품평 탭이 늘어난 것도 같은 구조 때문이다. 목록 SQL은 본문을 읽지 않지만, 본문이 행 안에 들어 있으므로 리프 한 블록에 담기는 행이 줄었다. 행 길이가 약 60바이트에서 650바이트로 커지면서 리프는 33블록에서 약 690블록이 됐고, 한 상품의 50건이 리프 7블록에 걸친다(Buffers 3 → 8). 고치는 방법은 OVERFLOW 세그먼트를 두고 INCLUDING으로 자르는 것이다. INCLUDING EVL_PT로 두면 PK와 CUS_ID, EVL_DT, EVL_PT까지는 리프에 남고 그 뒤의 EVL_DSC는 overflow 세그먼트로 간다. INCLUDING은 컬럼 순서로 자르므로, 새로 만들 때 EVL_DSC를 맨 뒤로 옮긴다. 원래 순서(EVL_DSC가 넷째)대로 두면 목록이 읽는 EVL_DT와 EVL_PT까지 overflow로 가 버린다. 등록 SQL은 컬럼 목록을 적어 두었으므로 순서가 바뀌어도 그대로 돈다. 고친 뒤 상품평 탭은 리프만 읽어 다시 3블록으로 끝나고, 긴 상품평도 등록된다. 본문 팝업은 리프에서 overflow 주소를 얻어 overflow 블록을 하나 더 읽는다(3블록). 대가는 overflow를 읽을 때마다 행 단위 랜덤 액세스가 생긴다는 점이다. 목록에 본문 미리보기를 넣는 식으로 본문을 여러 건 읽는 화면이 생기면 IOT의 이점이 사라진다. 그래서 INCLUDING 경계는 자주 읽는 컬럼과 드물게 읽는 컬럼 사이에 둔다. 테이블을 다시 만드는 동안에는 등록을 막거나 DBMS_REDEFINITION으로 온라인 재정의를 해야 한다.

해설

함정: IOT를 버리고 힙 테이블로 돌아가면 등록 오류는 사라지지만 상품평 탭은 다시 47블록 넘게 읽는다(ACC-049). 고칠 것은 IOT가 아니라 리프에 무엇을 담느냐다.
PCTTHRESHOLD만 낮추고 INCLUDING을 주지 않으면 한도를 넘는 긴 행만 잘려 overflow로 간다. 600바이트 안팎의 보통 본문은 여전히 리프에 남아 목록이 부푼다.
ALTER TABLE … MOVE ONLINE에 OVERFLOW와 INCLUDING을 주어 제자리에서 다시 짓는 방법도 있다. 다만 컬럼 순서는 그대로이므로, 이 테이블에서는 EVL_DSC 앞에서 자르면 EVL_DT와 EVL_PT가 overflow로 가고 뒤에서 자르면 본문이 리프에 남는다. 그래서 새로 만든다.
본문을 별도 테이블(같은 PK)로 떼어 내는 설계도 같은 효과를 낸다. overflow와 달리 SQL을 두 테이블로 고쳐야 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 본문 길이 분포와 리프 수는 어림값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 본문 길이 분포와 리프 수는 어림값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황