• 인덱스(Oracle 8i Release 1 이후) 및 테이블(Oracle 9i Release 2)에 데이터 압축 가능
• 압축 알고리즘은 반복되는 정보가 발생할 때마다 한 번씩 특정 블록에 쓰지 않고 반복되는 정보를 떼어내어 특벙 블록에 한번씩만 저장

인덱스 키압축

Ex) DBA_OBJECTS 에서 (OWNER, OBJECT_TYPE, OBJECT_NAME)에 인덱스를 생성한 경우

압축 전

(USER1,TABLE,T1) (USER2,TABLE,T2) (USER2,TABLE,T3) (USER2,TABLE,T4) (USER2,TABLE,T5) (USER2,TABLE,T6)
(USER2,TABLE,T7) (USER2,INDEX,I1) (USER2,INDEX,I2) (USER2,INDEX,I3) (USER2,INDEX,I4)

압축 후

USER1,TABLE T1 T2 T3 T4 T5 T6
T7 USER2,INDEXI1 I2 I3 I4

장점

• 1. 디스크 공간을 적게 소비하므로 저장소가 절약된다.
• 2. 시스템이 수행하는 물리적인 I/O의 수를 줄여준다.
• 3. 버퍼 캐시 효율성을 높여 준다. 캐시에 저장되어야 할 블록의 수가 훨씬 적을 뿐만 아니라 인덱스 블록이 압축된 채로 저장되어 캐시에 저장된다.

단점

• 1. 블록당 행 엔트리의 수를 증가시킴으로써 이미 촘촘한 이들 데이터 구조에 대한 경합을 악화 시킨다.
• 2. 구조가 복잡하기 때문에 실행 시간에 처리를 위해 보다 많은 cpu 시간을 필요로 한다. 이러한 현상은 INSERT 뿐만 아니라 SELECT 연산도 포함한다.

• Ex) 일반 인덱스와 압축된 인덱스의 차이 분석 비교

SQL> create table t1 as select * from dba_objects; 
테이블이 생성되었습니다. 

SQL> insert /*\+ APPEND \*/ into t1 select * from t1; 
49936 개의 행이 만들어졌습니다. 

SQL> commit; 
커밋이 완료되었습니다. 

SQL> insert /*\+ APPEND \*/ into t1 select * from t1; 
99872 개의 행이 만들어졌습니다. 

SQL> commit; 
커밋이 완료되었습니다. 

SQL> create table t2 as select * from t1; 
테이블이 생성되었습니다. 

SQL> create index uncompressed_idx on t1(owner,object_type,object_name); 
인덱스가 생성되었습니다. 

SQL> create index compressed_idx on t2(owner,object_type,object_name) compress 3; 
인덱스가 생성되었습니다. 

SQL> analyze index uncompressed_idx validate structure; 
인덱스가 분석되었습니다. 

SQL> create table index_stat_copy as select * from index_stats; 
테이블이 생성되었습니다. 

SQL> analyze index compressed_idx validate structure; 
인덱스가 분석되었습니다. 

SQL> insert into index_stat_copy select * from index_stats; 
1 개의 행이 만들어졌습니다. 

SQL> commit; 
커밋이 완료되었습니다. 

• 결과

SQL> print x 
NAME UNCOMPRESSED COMPRESSED DIFF PCT 
\---------------\--\--\- --\--\---\--\--\- --\--\-\--\--\- --\--\-\--\--\- --\--\-----\- 
BLKS_GETS_PER_ACCESS 5.50477749 5.50477749 0 100 
BLOCKS 1408 640 768 45.45 
BR_BLKS 9 4 5 44.44 
BR_BLK_LEN 8032 8032 0 100 \\
BR_ROWS 1382 618 764 44.72 
BR_ROWS_LEN 62454 23745 38709 38.02 
BTREE_SPACE 11136288 4981652 6154636 44.73 
DEL_LF_ROWS 0 0 0 
DEL_LF_ROWS_LEN 0 0 0 
DISTINCT_KEYS 49817 49817 0 100 
HEIGHT 3 3 0 100 
LF_BLKS 1383 619 764 44.76 
LF_BLK_LEN 8000 7996 4 99.95 
LF_ROWS 199744 199744 0 100 
LF_ROWS_LEN 9896776 2197184 7699592 22.2 
MOST_REPEATED_KEY 16 16 0 100 
OPT_CMPR_COUNT 3 3 0 100 
OPT_CMPR_PCTSAVE 55 0 55 0 
PCT_USED 90 90 0 100 
PRE_ROWS 0 50064 \-50064 
PRE_ROWS_LEN 0 2230025 \-2230025 
ROWS_PER_KEY 4.00955497 4.00955497 0 100 
USED_SPACE 9959230 4450954 5508276 44.69 
23 개의 행이 선택되었습니다. 

• BLOCKS: 압축률이 50% 이상
• OPT_CMPR_COUNT: 키 압축을 할 경우 공간 절약 비율
• PRE_ROWS, PRE_ROWS_LEN:인덱스에서 고유한 접두어 키의 대한 정보임
• 압축되지 않은 인덱스에는 유일한 행이 없으나, 압축된 인덱스에서는 공통된 행은 분리되므로 해당 값이 존재함

• CPU 사용량 확인

SELECT * 
FROM 
T1 WHERE OWNER = :B3 AND OBJECT_NAME = :B2 AND OBJECT_TYPE = :B1 

call count cpu elapsed disk query current rows 
\---\--\-- --\--\----------------------------------------------\-  
Parse 1 0.00 0.00 0 0 0 0 
Execute 199744 2.26 2.16 0 0 0 0 
Fetch 199744 6.12 6.14 90 1408736 0 806336 
\---\--\-- --\--\----------------------------------------------\-  

*total 399489 8.39 8.31 90 1408736 0 806336* 

Misses in library cache during parse: 1 
Optimizer mode: ALL_ROWS 
Parsing user id: 5 (recursive depth: 1) 
Rows Row Source Operation 
\---\--\-- --\--\----------------------------------------------\- 

806336 TABLE ACCESS BY INDEX ROWID T1 (cr=1408736 pr=90 pw=0 time=4550762 us) 
806336 INDEX RANGE SCAN UNCOMPRESSED_IDX (cr=604832 pr=0 pw=0 time=2182528 us)(object id 51611) 

SELECT * 
FROM 
T2 WHERE OWNER = :B3 AND OBJECT_NAME = :B2 AND OBJECT_TYPE = :B1 

call count cpu elapsed disk query current rows 
\---\--\-- --\--\----------------------------------------------\-  
Parse 1 0.00 0.00 0 0 0 0 
Execute 199744 2.31 2.22 0 0 0 0 
Fetch 199744 6.45 6.41 0 1405948 0 806336 
\---\--\-- --\--\----------------------------------------------\-  

*total 399489 8.76 8.64 0 1405948 0 806336* 
Misses in library cache during parse: 1 
Optimizer mode: ALL_ROWS 
Parsing user id: 5 (recursive depth: 1) 
Rows Row Source Operation 

\---\--\-- --\--\----------------------------------------------\- 

806336 TABLE ACCESS BY INDEX ROWID T2 (cr=1405948 pr=0 pw=0 time=4716449 us) 
806336 INDEX RANGE SCAN COMPRESSED_IDX (cr=601716 pr=0 pw=0 time=2397303 us)(object id 51612) |

• CPU 시간은 차이가 거의 없음(약 4~5%)
• LogicalI/O 수는 거의 값음(인덱스 높이가 같기 때문)

Ex) Insert 연산 시 차이 비교

• 압축을 사용하지 않은 테이블의 삽입 소요 시간

 insert into t1 select * from dba_objects 구문을 총 5회 수행

| call count cpu elapsed disk query current rows
 \------\- \------ \-------\- \---------\- \---------\- \---------\- \---------- \---------\-
 Parse 7 0.01 0.03 0 0 0 0
 Execute 7 10.96 16.59 920 40935 679662 249820
 Fetch 0 0.00 0.00 0 0 0 0
 \------\- \------ \-------\- \---------\- \---------\- \---------\- \---------- \---------\-
 total 14 *10.98 16.63* 920 40935 679662 249820 

• 압축을 사용한 테이블의 삽입 소요 시간

insert into t2 select * from dba_objects 구문을 총 5회 수행
| call count cpu elapsed disk query current rows
 \------\- \------ \-------\- \---------\- \---------\- \---------\- \---------- \---------\-
 Parse 7 0.01 0.01 0 0 0 0
 Execute 7 19.81 28.76 921 35918 682410 249820
 Fetch 0 0.00 0.00 0 0 0 0
 \------\- \------ \-------\- \---------\- \---------\- \---------\- \---------- \---------\-
 total 14 *19.82 28.78* 921 35918 682410 249820 

Ex) Delete 연산 시 차이 비교

• 압축을 사용하지 않은 테이블의 삭제 소요 시간

 delete from t1 수행
| call count cpu elapsed disk query current rows
 \------\- \------ \-------\- \---------\- \---------\- \---------\- \---------- \---------\-
 Parse 1 0.00 0.00 0 0 0 0
 Execute 1 0.00 27.00 5721 10427 1033820 249820
 Fetch 0 0.00 0.00 0 0 0 0
 \------\- \------ \-------\- \---------\- \---------\- \---------\- \---------- \---------\-
 total 2 0.00 *27.00* 5721 10427 1033820 249820 |
\- 압축을 사용한 테이블의 삭제 소요 시간
 delete from t2 수행
| call count cpu elapsed disk query current rows
 \------\- \------ \-------\- \---------\- \---------\- \---------\- \---------- \---------\-
 Parse 1 0.00 0.00 0 0 0 0
 Execute 1 0.00 34.63 2924 6893 1026514 249820
 Fetch 0 0.00 0.00 0 0 0 0
 \------\- \------ \-------\- \---------\- \---------\- \---------\- \---------- \---------\-
 total 2 0.00 *34.63* 2924 6893 1026514 249820 

읽기 전용/준읽기 전용 테이블에 대한 테이블 압축 사용

• 테이블 압축은 8i IOT에서부터 시작(인덱스 압축을 지원하기 시작한 버전임)
• 보통의 테이블 압축은 9i R2 부터 지원하기 시작

특징

• 1. 테이블 압축은 집계 블록 수준에서 수행됨. 구축하면서 구축 중인 모든 행과 열에 대해서만 압축 수행(이 점이 인덱스 압축과 큰 차이임)
• 2.보통의 DML 작업에서 인덱스는 압축을 유지하나 테이블은 압축으로 구축된 이후에는 새로운 데이터에 대해서는압축하지 않음
• 그러므로 OLTP 환경에서는 적당하지 않은 선택임. 읽기전용 또는 준읽기전용 테이블에 적합
• OLTP 성 테이블에 갱신이 일어나는 경우 압축을 풀어야 하기 때문에 성능상 불리해짐
• 3. 압축된 테이블의 pctfree는 0의 값임

압축이 가능한 명령문

• 1. CREATE TABLE AS SELECT
• 2. INSERT /*\+ APPEND \*/
• 3. SQLLDR DIRECT=Y
• 4. ALTER TABLE MOVE

테이블 압축이 유용한 경우

• 1. 읽기 전용 또는 준읽기 전용인 대규모의 정적 참조 정보
• 2. 대량 작업이 일상적인 데이터 웨어하우징 환경
• 3. 분할된 테이블에 저장된 감사 추적 정보

테이블 압축 저장 내부 구조

• EX)

SCOTT,TABLE,EMP SCOTT,TABLE,DEPT SCOTT,TABLE,BONUS
SCOTT,INDEX,EMP_PK SCOTT,INDEX,ENAME_IDX SCOTT,INDEX,DEPT_PK
SCOTT,INDEX,EMP_DEPT SCOTT,INDEX,DNAME_IDX SCOTT,PROCEDURE,P1
SCOTT,PROCEDURE,P2 SCOTT,PROCEDURE,P3 SCOTT,PROCEDURE,P4

SCOTT=<A>,TABLE=<B>,
INDEX=<C>,PROCEDURE=<D>
<A>,<B>,EMP <A>,<B>,DEPT <A>,<B>,BONUS <A>,<C>,EMP_PK
<A>,<C>,ENAME_IDX <A>,<C>,DEPT_PK <A>,<C>,EMP_DEPT <A>,<C>,DNAME_IDX
<A>,<D>,P1 <A>,<D>,P2 <A>,<D>,P3 <A>,<D>,P4

Ex)예제

• 압축되지 않은 테이블, 정렬을 수행하여 압축한 테이블, 무작위로 정렬을 수행하여 압축한 테이블의 세 경우에 대한 크기 비교

SQL> create table uncompressed pctfree 0 
 2 as select * from dba_objects order by owner,object_type,object_name; 
테이블이 생성되었습니다. 

SQL> analyze table uncompressed compute statistics; 
테이블이 분석되었습니다. 
 
SQL> create table compressed pctfree 0 compress 
 2 as select * from dba_objects order by owner,object_type,object_name; 
테이블이 생성되었습니다. 

SQL> analyze table compressed compute statistics; 
테이블이 분석되었습니다. 

SQL> create table compressed_random pctfree 0 compress 
 2 as select * from dba_objects order by dbms_random.random; 
테이블이 생성되었습니다. 

SQL> analyze table compressed_random compute statistics; 
테이블이 분석되었습니다. 

• 결과

SQL> select cblks comp_blks,crblks compr_blks, uncblks uncomp_blks, 
 2 round(cblks/uncblks*100,2) pct1, 
 3 round(crblks/uncblks*100,2) pct2 
 4 from ( 
 5 select max(decode(table_name,'COMPRESSED',blocks,null)) cblks, 
 6 max(decode(table_name,'COMPRESSED_RANDOM',blocks,null)) crblks, 
 7 max(decode(table_name,'UNCOMPRESSED',blocks,null)) uncblks 
 8 from user_tables 
 9 where table_name in ( 'COMPRESSED', 'COMPRESSED_RANDOM', 'UNCOMPRESSED' ) 
10 ) 
11 / 

COMP_BLKS COMPR_BLKS UNCOMP_BLKS PCT1 PCT2 
\-----\--\--\- --\--\-\--\--\- --\--\--\--\--\- --\--\-\--\--\- --\--\-----\- 
 345 451 617 55.92 73.1 

• 특정 컬럼에 대해 정렬하는 경우 구축 중에 더 많은 압축을 기대할 수 있음
  (정렬을 하지 않는다고 하더라도 많은 압축률을 보였음)
• 컬럼의 정렬이 압축의 큰 영향을 미치며 다음의 경우 처럼 TIMESTAMP 처럼 넓고 반복되는 값이 많은 컬럼으로 압축하면 효과는 더욱 큼
  압축으로 발생할 수 있는 공간 절약 보다는 목적에 따라 컬럼 선정을 잘해야 함

 
 SQL> ed 
file afiedt.buf(이)가 기록되었습니다 
 1 create table compressed_timestamp pctfree 0 compress 
 2\* as select * from dba_objects order by timestamp 
SQL> / 
테이블이 생성되었습니다. 
SQL> analyze table compressed_timestamp compute statistics; 
테이블이 분석되었습니다. 
SQL> select blocks from user_tables where table_name='COMPRESSED_TIMESTAMP'; 
 BLOCKS 
\---------\- 
 *209* 

정적 정보와 데이터 웨어하우징의 압축된 테이블

순서

• 1. 테이블을 분석하여 최상의 정렬 열을 찾아 압축을 극대화 한다.
• 2. ORDER BY와 함께 CREATE TABLE COPY OF TABLE compress AS SELECT를 사용한다.
• 3. 오래된 압축되지 않은 테이블을 삭제하고 이 사본의 이름을 변경한다.