오라클 19c 테이블 compress 압축률 비교

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.15.0.0

 

방법 : 오라클 19c 테이블 compress 압축률 비교

오라클 19c 환경에서 테이블 압축 시 단일 insert 구문과 itas(array) insert 시 압축률이 얼마나 차이나는지 확인해봄

추가로 압축 시 dml 시 걸리는 시간도 같이 확인해봄

 

 

테이블 압축 옵션 설명
ROW STORE COMPRESS [BASIC] : 
기본 테이블 압축 기능으로 Direct Path Insert 또는 벌크 인서트시 데이터가 압축됨, 이외 일반 Insert 시에는 압축되지 않음

ROW STORE COMPRESS ADVANCED : 
고급 테이블 압축 기능으로 Direct Path Insert 또는 벌크 인서트, 일반 Insert시 모두 데이터가 압축됨

COLUMN STORE COMPRESS FOR QUERY [LOW|HIGH] : 
웨어하우스 압축 기능으로 이 압축 옵션 사용시 CPU 오버헤드가 높아질 수 있음
업데이트된 로우와 Direct Path Insert 또는 벌크 인서트가 아닌 일반 Insert 로 삽입된 로우는 컬럼 형식이 아닌 로우 형식으로 저장되기 때문에 압축 수준이 낮음
- LOW 옵션 : 로드 성능이 중요한 환경에서 사용해야함, HIGH 옵션으로 압축한 데이터보다 로딩 속도가 빠름
- HIGH 옵션 : Exadata 환경에서 HCC(Hybrid Columnar Compression) 기능을 사용할 때 우수한 압축 성능을 제공함
본문에선 테스트하지 않음

COLUMN STORE COMPRESS FOR ARCHIVE [LOW|HIGH] : 
아카이브 압축 기능으로 이 압축 옵션 사용시 CPU 오버헤드가 높아질 수 있음
업데이트된 로우와 Direct Path Insert 또는 벌크 인서트가 아닌 일반 Insert 로 삽입된 로우는 컬럼 형식이 아닌 로우 형식으로 저장되기 때문에 압축 수준이 낮음
- LOW 옵션 : 기본 아카이브 압축 모드로 높은 압축 수준을 제공하며 자주 액세스하지 않는 데이터에 적합한 압축 옵션임
- HIGH 옵션 : 거의 액세스되지 않는 데이터에 사용해야함
본문에선 테스트하지 않음

 

 

테스트1 itas insert 문 compress 비교
시간 비교용 sqlplus 옵션 on

SQL> set timing on

 

 

데이터 삽입용 임시 테이블 생성

SQL> 
DROP TABLE MAXTEST PURGE;
CREATE TABLE MAXTEST(COLA VARCHAR2(20), COLB NUMBER, COLC NUMBER, 
COLD VARCHAR2(30), COLE VARCHAR2(30), COLF VARCHAR2(30), 
COLG NUMBER, COLH VARCHAR2(30), COLI VARCHAR2(30))
TABLESPACE USERS;

 

 

임시 테이블 데이터 삽입

SQL>
DECLARE
TYPE tbl_ins IS TABLE OF MAXTEST%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR j in 1..10 LOOP 
FOR i IN 1..1400000 LOOP 
   w_ins(i).COLA :=i;
   w_ins(i).COLB :=300000;
   w_ins(i).COLC :=99;
   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';
   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
   w_ins(i).COLG :=9999999;
   w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
   w_ins(i).COLI :='IIIIIIIIIIIIIIIIIIIIIIIIII';
END LOOP;
   FORALL i in 1..1400000 INSERT INTO MAXTEST VALUES w_ins(i);
   COMMIT;
END LOOP;
END;
/

 

 

테이블 row 확인

SQL> select count(*) from MAXTEST ;
 
  COUNT(*)
----------
  14000000

 

 

압축 테스트용 테이블 삭제

SQL>
drop table test_nocomp purge;
drop table test_basic purge;
drop table test_advanced purge;
drop table test_advanced_ap purge;

 

 

압축 테스트용 테이블 생성(_ap 테이블은 append 힌트 삽입용 테이블)

SQL>
create table test_nocomp
tablespace users
as
select *
from MAXTEST
where 1 = 2 ;
 
create table test_basic
tablespace users
ROW STORE COMPRESS BASIC
as
select *
from MAXTEST
where 1 = 2 ;
 
create table test_advanced
tablespace users
ROW STORE COMPRESS ADVANCED
as
select *
from MAXTEST
where 1 = 2 ;
 
create table test_advanced_ap
tablespace users
ROW STORE COMPRESS ADVANCED
as
select *
from MAXTEST
where 1 = 2 ;

 

 

*테이블 설명
test_nocomp : 미압축 대상 테이블
test_basic : BASIC 압축 대상 테이블(APPEND 옵션 사용 INSERT)
test_advanced : ADVANCED 압축 대상 테이블
test_advanced_ap : ADVANCED 압축 대상 테이블(APPEND 옵션 사용 INSERT)

 

 

데이터 삽입

SQL>
insert into test_nocomp
select * from MAXTEST ;
commit;
Elapsed: 00:00:35.57
 
insert /*+ APPEND */ into test_basic
select * from MAXTEST ;
commit;
Elapsed: 00:00:16.93
 
insert into test_advanced
select * from MAXTEST ;
commit;
Elapsed: 00:00:43.99
 
insert /*+ APPEND */ into test_advanced_ap
select * from MAXTEST ;
commit;
Elapsed: 00:00:17.26

itas insert 시 시간은 basic 압축을 사용하고 append 힌트를 사용 했을때가 가장 빠름

 

 

용량 확인

SQL>
col segment_name for a20
col segment_type for a20
select segment_name, segment_type, blocks, bytes
, round(bytes/(select bytes from dba_segments where segment_name = 'TEST_NOCOMP')*100, 2)||'%' per
from dba_segments
where segment_type = upper('table')
and segment_name like upper('test%');
 
SEGMENT_NAME         SEGMENT_TYPE             BLOCKS      BYTES PER
-------------------- -------------------- ---------- ---------- ----------
TEST_ADVANCED        TABLE                     26624  218103808 10.48%
TEST_ADVANCED_AP     TABLE                     26624  218103808 10.48%
TEST_BASIC           TABLE                     24576  201326592 9.67%
TEST_NOCOMP          TABLE                    254080 2081423360 100%

BASIC 옵션과 ADVANCED 옵션 모두 압축이 정상적으로 동작함
(ADVANCED 옵션 사용시 APPEND 옵션을 사용한 경우와 하지 않은경우 압축률이 동일함)
압축되지 않은 테이블(TEST_NOCOMP)에 비해 
BASIC 압축테이블(TEST_BASIC)은 91%, ADVANCED 압축테이블(TEST_ADVANCED)은 90% 정도 압축됨
(ADVANCED 보다 BASIC이 압축율이 더 높은게 특이함)

 

 

테스트2 단일 insert 문 compress 비교
압축 테스트용 테이블 삭제2

SQL>
drop table test_nocomp2 purge ;
drop table test_basic2 purge ;
drop table test_advanced2 purge ;

 

 

압축 테스트용 테이블 생성2

SQL>
create table test_nocomp2 (col1 number, col2 number)
tablespace users ;
 
create table test_basic2 (col1 number, col2 number)
tablespace users
ROW STORE COMPRESS BASIC;
 
create table test_advanced2 (col1 number, col2 number)
tablespace users
ROW STORE COMPRESS ADVANCED;

 

 

*테이블 설명
test_nocomp2 : 미압축 대상 테이블
test_basic2 : BASIC 압축 대상 테이블
test_advanced2 : ADVANCED 압축 대상 테이블

 

 

단일 insert 문으로 데이터 삽입

SQL> 
begin
for j in 1 .. 140 loop
for i in 1 .. 100000 loop
insert into test_nocomp2 values(i,j);
end loop; 
commit;
end loop;
end;
/
Elapsed: 00:02:35.20
 
begin
for j in 1 .. 140 loop
for i in 1 .. 100000 loop
insert into test_basic2 values(i,j);
end loop; 
commit;
end loop;
end;
/
Elapsed: 00:02:38.82
 
begin
for j in 1 .. 140 loop
for i in 1 .. 100000 loop
insert into test_advanced2 values(i,j);
end loop; 
commit;
end loop;
end;
/
Elapsed: 00:03:06.65

단일 insert 시 시간은 압축을 사용하지 않을때가 가장 빠름

 

 

용량 확인

SQL>
col segment_name for a20
col segment_type for a20
col per for a10
select segment_name, segment_type, blocks, bytes
, round(bytes/(select bytes from dba_segments where segment_name = 'TEST_NOCOMP2')*100, 2)||'%' per
from dba_segments a
where segment_type = upper('table')
and segment_name like upper('test%2');
 
SEGMENT_NAME         SEGMENT_TYPE             BLOCKS      BYTES PER
-------------------- -------------------- ---------- ---------- ----------
TEST_ADVANCED2       TABLE                     22528  184549376 84.62%
TEST_BASIC2          TABLE                     23552  192937984 88.46%
TEST_NOCOMP2         TABLE                     26624  218103808 100%

압축되지 않은 테이블(TEST_NOCOMP2)에 비해 
BASIC 압축테이블(TEST_BASIC2)은 12%, ADVANCED 압축테이블(TEST_ADVANCED2)은 16% 정도 압축됨

 

 

테이블 압축 레벨 확인

SQL>
col table_name for a20
SELECT table_name, compression, compress_for
FROM dba_tables
where table_name like 'TEST%';
 
TABLE_NAME           COMPRESS COMPRESS_FOR
-------------------- -------- ------------------------------
TEST_ADVANCED        ENABLED  ADVANCED
TEST_ADVANCED_AP     ENABLED  ADVANCED
TEST_ADVANCED2       ENABLED  ADVANCED
TEST_BASIC           ENABLED  BASIC
TEST_BASIC2          ENABLED  BASIC
TEST_NOCOMP          DISABLED
TEST_NOCOMP2         DISABLED

 

 

결론 : 

itas insert 의 경우

BASIC 옵션과 ADVANCED 옵션 모두 압축이 정상적으로 동작함
(ADVANCED 옵션 사용시 APPEND 옵션을 사용한 경우와 하지 않은경우 압축률이 동일함)
압축되지 않은 테이블(TEST_NOCOMP)에 비해 
BASIC 압축테이블(TEST_BASIC)은 91%, ADVANCED 압축테이블(TEST_ADVANCED)은 90% 정도 압축됨

단일 insert 의 경우 

압축되지 않은 테이블(TEST_NOCOMP2)에 비해 
BASIC 압축테이블(TEST_BASIC2)은 12%, ADVANCED 압축테이블(TEST_ADVANCED2)은 16% 정도 압축됨

 

 

참조 : 

https://positivemh.tistory.com/340

오라클 테이블 compress 정리(10g, 11g, 12c, 18c)

https://positivemh.tistory.com/475

오라클 Table Compress 기능 중 oltp와 advanced 옵션 차이점

https://positivemh.tistory.com/929

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tables.html#GUID-34D15DD1-0925-4C9A-BE8A-3EE91671E526
https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/partition-table-compression.html#GUID-F26AFD78-DC1D-4E6B-9B37-375C59FD1787
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/configuring-database-for-performance.html#GUID-16937141-895F-4D1C-B431-B129157B515E
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tablespaces.html#GUID-207D9DF2-ED00-4A40-AC7B-49A4EAD061E6

Primary Note for OLTP Compression (Doc ID 1223705.1)

How to Compress a Partitioned Table (Doc ID 1516932.1)

How to Compress an Existing Table (Doc ID 1080816.1)

11g Table compression : Overview (Doc ID 466362.1)

Advanced Table Compression (Doc ID 1060693.1)

Can Both HCC and Advanced Compression Be Used On The Same Table? (Doc ID 1309704.1)

Difference Between Basic Table Compression And Advanced Compression (Doc ID 1548187.1)

All About Advanced Table Compression (Overview, Usage, Examples, Restrictions) (Doc ID 882712.1)

Table may contain records compressed with Basic as well as Advance compression option (Doc ID 1990416.1)

Basic Compression in Database (Doc ID 2861878.1)