휴면 고객 판정 배치가 고객 주문 인덱스를 통째로 읽는데, 인덱스를 압축하면 얼마나 주느냐고 묻는다

기초DDL한빛유통135 / 700

상황

매일 밤 「휴면 고객 판정」 배치는 고객마다 마지막 주문일을 구해 여섯 달 넘게 주문이 없는 고객을 휴면으로 바꾼다. 이 단계가 고객 주문 인덱스를 처음부터 끝까지 읽는다. 오과장이 묻는다. "05화에서 인덱스가 테이블보다 크다고 했지. 이 인덱스를 압축하면 얼마나 줄고 무엇이 빨라지나? 압축 컬럼 수는 몇으로 하나?"

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · CUS_ID VARCHAR2(40)의 값은 'CUS_000123' 꼴 10글자 · 고객 2,000명, 고객당 500건 · 고객 한 명의 주문은 219개 날짜에 몰려 있다(한 날짜에 2 ~ 3건)
인덱스 IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT): BLEVEL 2 · 리프 약 4,110블록(리프 한 블록에 약 243항목) · 압축하지 않았다 · 통계 최신
이 인덱스를 쓰는 SQL: 휴면 판정 배치(하루 1번) · 「고객 주문 이력」(고객 한 명의 한 달, 하루 약 6천 번) · 주문은 운영에서 계속 INSERT된다

원본 SQL

sql

SELECT CUS_ID, MAX(ORD_DT) AS last_ord_dt
  FROM T_ORD_BIG
 GROUP BY CUS_ID

실행계획 학습용 예시 · 고객별 분포는 적재 스크립트 그대로 · 압축 뒤 리프 수는 어림값

xplan

---------------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |      1 |        |   2000 |00:00:00.21 |    4128 |
|   1 |  HASH GROUP BY         |                   |      1 |   2000 |   2000 |00:00:00.21 |    4128 |
|   2 |   INDEX FAST FULL SCAN | IX_ORD_BIG_CUS_DT |      1 |   1000K|   1000K|00:00:00.08 |    4128 |
---------------------------------------------------------------------------------------------------

문제

인덱스 키 압축(COMPRESS n)이 리프 블록에서 무엇을 줄이는지 설명하고, 이 인덱스에서 압축할 컬럼 수를 컬럼별 값의 반복 정도로 정하시오.
압축 DDL을 작성하고, 압축 뒤 휴면 판정 배치와 「고객 주문 이력」의 읽는 양이 각각 어떻게 되는지와 압축의 대가를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 선두 CUS_ID 하나만 압축한다 : 같은 고객 500건의 'CUS_000123' 을 리프 블록마다 한 번만 적는다
ALTER INDEX IX_ORD_BIG_CUS_DT REBUILD COMPRESS 1 ONLINE;
EXEC DBMS_STATS.GATHER_INDEX_STATS(USER, 'IX_ORD_BIG_CUS_DT');

SELECT CUS_ID, MAX(ORD_DT) AS last_ord_dt     -- SQL 은 그대로
  FROM T_ORD_BIG
 GROUP BY CUS_ID;

개선 후 실행계획 학습용 예시 · 고객별 분포는 적재 스크립트 그대로 · 압축 뒤 리프 수는 어림값

xplan

---------------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |      1 |        |   2000 |00:00:00.18 |    2648 |
|   1 |  HASH GROUP BY         |                   |      1 |   2000 |   2000 |00:00:00.18 |    2648 |
|   2 |   INDEX FAST FULL SCAN | IX_ORD_BIG_CUS_DT |      1 |   1000K|   1000K|00:00:00.06 |    2648 |
---------------------------------------------------------------------------------------------------

모범 답안 서술

키 압축은 정렬된 리프 블록 안에서 앞쪽 컬럼 값이 되풀이되면, 그 값을 블록마다 한 번만 적고(접두 항목) 행마다 뒤쪽 컬럼과 ROWID만 남기는 기능이다. 항목 하나가 짧아지므로 리프 한 블록에 더 많은 항목이 들어가고 리프 수가 준다. 압축할 컬럼 수는 그 앞부분이 몇 번 되풀이되느냐로 정한다. CUS_ID는 고객 한 명에 500건이라 'CUS_000123'(10바이트)이 500번 연달아 나온다. 반면 (CUS_ID, ORD_DT) 묶음은 2 ~ 3번만 되풀이된다. 묶음마다 접두 항목을 따로 적어야 하므로 COMPRESS 2는 덜 준다. 그래서 COMPRESS 1이다. COMPRESS 1로 다시 만들면 항목이 약 30바이트에서 약 19바이트가 되어 리프는 약 4,110 → 2,630블록이 된다. 휴면 판정 배치는 인덱스 전체를 읽으므로 그만큼 줄어든다(Buffers 4,128 → 약 2,648). 「고객 주문 이력」은 거의 그대로다. 고객 한 명의 한 달은 열몇 건이라 지금도 리프 한 블록 안에서 끝나고, 읽는 양의 대부분은 ROWID로 찾아가는 테이블 블록이다. 압축은 리프를 줄일 뿐 테이블 랜덤 액세스는 줄이지 못한다. 대가는 CPU다. 항목을 읽을 때 접두 항목과 합쳐야 하고, INSERT 때도 접두 항목을 찾아 맞춰야 하므로 조금 더 든다. 운영 중에 다시 만들려면 ONLINE으로 해야 주문 INSERT를 막지 않는다.

해설

함정: COMPRESS에 숫자를 빼면 비유일 인덱스는 컬럼 전부(여기서는 2)를 압축한다. 되풀이가 2 ~ 3번뿐인 묶음까지 압축하므로 COMPRESS 1보다 덜 준다. 숫자를 꼭 적는다.
압축률은 미리 잴 수 있다. ANALYZE INDEX … VALIDATE STRUCTURE 뒤 INDEX_STATS의 OPT_CMPR_COUNT(권하는 컬럼 수)와 OPT_CMPR_PCTSAVE(줄어드는 비율)를 본다. 이 명령은 도는 동안 테이블의 DML을 막으므로 복사본에서 한다(ACC-061).
선두 컬럼이 많이 되풀이될수록 압축이 잘 된다. 하지만 컬럼 순서는 압축률이 아니라 SQL의 조건(= 조건 앞, 범위 뒤)으로 정하고, 압축은 정해진 순서 위에서 고른다.
인덱스 합계가 테이블보다 크다는 05화의 문제는 압축으로도 줄지만, 쓰지 않는 인덱스를 지우는 쪽이 먼저다(교재 14화의 적재 청구서).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 고객별 분포는 적재 스크립트 그대로 · 압축 뒤 리프 수는 어림값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 고객별 분포는 적재 스크립트 그대로 · 압축 뒤 리프 수는 어림값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황