오라클 19c 인덱스 compress 압축률 비교

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.15.0.0

 

방법 : 오라클 19c 인덱스 compress 압축률 비교

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

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

 

 

인덱스 압축 옵션 설명
COMPRESS ADVANCED LOW : 
HIGH 옵션보다 인덱스를 덜 압축하지만 인덱스 액세스가 더 빠름
인덱스 압축시 최소 2개 컬럼을 포함해야함
COMPRESS ADVANCED LOW 옵션 사용 시 DB 최소 버전은 12.1.0 이상이어야함

COMPRESS ADVANCED HIGH : 
LOW 옵션보다 인덱스를 더 많이 압축하지만 인덱스 엑세스가 더 느림
인덱스 압축시 최소 1개 컬럼을 포함해야함
COMPRESS ADVANCED HIGH 옵션 사용 시 DB 최소 버전은 12.2.0 이상이어야함

 

 

테스트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_advanced_low purge;
drop table test_advanced_low_ap purge;
drop table test_advanced_high purge;
drop table test_advanced_high_ap purge;

 

 

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

SQL>
create table test_nocomp
tablespace users
as
select *
from MAXTEST
where 1 = 2 ;
 
create table test_advanced_low 
tablespace users
as
select *
from MAXTEST
where 1 = 2 ;
 
create table test_advanced_low_ap 
tablespace users
as
select *
from MAXTEST
where 1 = 2 ;
 
create table test_advanced_high 
tablespace users
as
select *
from MAXTEST
where 1 = 2 ;
 
create table test_advanced_high_ap
tablespace users
as
select *
from MAXTEST
where 1 = 2 ;

 

 

*테이블 설명

test_nocomp : 인덱스 미압축 대상 테이블
test_advanced_low : 인덱스 LOW 압축 대상 테이블
test_advanced_low_ap : 인덱스 LOW 압축 대상 테이블(APPEND 옵션 사용 INSERT)
test_advanced_high : 인덱스 HIGH 압축 대상 테이블
test_advanced_high_ap : 인덱스 HIGH 압축 대상 테이블(APPEND 옵션 사용 INSERT)

 

 

인덱스 압축 테스트용 테이블에 인덱스 생성

SQL> 
create index ix_test_nocomp on test_nocomp(cola, colb, colc) tablespace users;
create index ix_test_advanced_low on test_advanced_low(cola, colb, colc) tablespace users COMPRESS ADVANCED LOW;
create index ix_test_advanced_low_ap on test_advanced_low_ap(cola, colb, colc) tablespace users COMPRESS ADVANCED LOW;
create index ix_test_advanced_high on test_advanced_high(cola, colb, colc) tablespace users COMPRESS ADVANCED HIGH;
create index ix_test_advanced_high_ap on test_advanced_high_ap(cola, colb, colc) tablespace users COMPRESS ADVANCED HIGH;

 

 

데이터 삽입

SQL>
insert into test_nocomp
select * from MAXTEST ;
commit;
Elapsed: 00:01:25.57
 
insert into test_advanced_low
select * from MAXTEST ;
commit;
Elapsed: 00:00:55.38
 
insert /*+ APPEND */ into test_advanced_low_ap
select * from MAXTEST ;
commit;
Elapsed: 00:01:00.16
 
insert into test_advanced_high
select * from MAXTEST ;
commit;
Elapsed: 00:05:39.90
 
insert /*+ APPEND */ into test_advanced_high_ap
select * from MAXTEST ;
commit;
Elapsed: 00:01:49.11

itas insert 시 시간은 advanced low 옵션을 사용하고 append 힌트를 사용하지 않았을때가 가장 빠름

 

 

용량 확인

SQL>
col segment_name for a30
col segment_type for a20
select segment_name, segment_type, blocks, bytes
, round(bytes/(select bytes from dba_segments where segment_name = 'IX_TEST_NOCOMP')*100, 2)||'%' per
from dba_segments
where segment_type = upper('index')
and segment_name like upper('ix_test%');
 
SEGMENT_NAME                   SEGMENT_TYPE             BLOCKS      BYTES PER
------------------------------ -------------------- ---------- ---------- ----------
IX_TEST_ADVANCED_HIGH          INDEX                     30720  251658240 44.78%
IX_TEST_ADVANCED_HIGH_AP       INDEX                     21504  176160768 31.34%
IX_TEST_ADVANCED_LOW           INDEX                     34944  286261248 50.93%
IX_TEST_ADVANCED_LOW_AP        INDEX                     23552  192937984 34.33%
IX_TEST_NOCOMP                 INDEX                     68608  562036736 100%

ADVANCED LOW옵션과 ADVANCED 옵션 모두 압축이 정상적으로 동작함
압축되지 않은 인덱스(IX_TEST_NOCOMP)에 비해 
APPEND 옵션을 넣지 않은 경우 ADVANCED LOW 압축 인덱스(IX_TEST_ADVANCED_LOW)는 50%,
APPEND 옵션을 넣은 경우 ADVANCED LOW 압축 인덱스(IX_TEST_ADVANCED_LOW_AP)는 66%,
APPEND 옵션을 넣지 않은 경우 ADVANCED HIGH 압축 인덱스(IX_TEST_ADVANCED_HIGH)는 56%,
APPEND 옵션을 넣은 경우 ADVANCED HIGH 압축 인덱스(IX_TEST_ADVANCED_HIGH_AP)는 69%
정도로 압축됨
각각 ADVANCED HIGH 옵션을 사용한 경우가 압축율이 더 좋고 APPEND 옵션을 넣은 경우가 압축율이 더 좋음

 

 

인덱스 압축 래밸 확인

SQL>
col table_name for a30
col index_name for a30
SELECT table_name, index_name, compression
FROM dba_indexes
where index_name like 'IX_TEST%';
 
TABLE_NAME                     INDEX_NAME                     COMPRESSION
------------------------------ ------------------------------ -------------
TEST_ADVANCED_HIGH             IX_TEST_ADVANCED_HIGH          ADVANCED HIGH
TEST_ADVANCED_HIGH_AP          IX_TEST_ADVANCED_HIGH_AP       ADVANCED HIGH
TEST_ADVANCED_LOW              IX_TEST_ADVANCED_LOW           ADVANCED LOW
TEST_ADVANCED_LOW_AP           IX_TEST_ADVANCED_LOW_AP        ADVANCED LOW
TEST_NOCOMP                    IX_TEST_NOCOMP                 DISABLED

 

 

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

SQL>
drop table test_nocomp2 purge;
drop table test_advanced_low2 purge;
drop table test_advanced_high2 purge;

 

 

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

SQL>
create table test_nocomp2 (col1 number, col2 number)
tablespace users ;
create table test_advanced_low2 (col1 number, col2 number)
tablespace users ;
create table test_advanced_high2 (col1 number, col2 number)
tablespace users ;

 

 

*테이블 설명

test_nocomp2 : 인덱스 미압축 대상 테이블
test_advanced_low2 : 인덱스 LOW 압축 대상 테이블
test_advanced_high2 : 인덱스 HIGH 압축 대상 테이블

 

 

인덱스 압축 테스트용 테이블에 인덱스 생성(압축 옵션 사용)

SQL> 
create index ix_test_nocomp2 on test_nocomp2(col1, col2) tablespace users;
create index ix_test_advanced_low2 on test_advanced_low2(col1, col2) tablespace users COMPRESS ADVANCED LOW;
create index ix_test_advanced_high2 on test_advanced_high2(col1, col2) tablespace users COMPRESS ADVANCED HIGH;

 

 

단일 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:05:36.09
 
begin
for j in 1 .. 140 loop
for i in 1 .. 100000 loop
insert into test_advanced_low2 values(i,j);
end loop; 
commit;
end loop;
end;
/
Elapsed: 00:05:05.18
 
begin
for j in 1 .. 140 loop
for i in 1 .. 100000 loop
insert into test_advanced_high2 values(i,j);
end loop; 
commit;
end loop;
end;
/
Elapsed: 00:10:03.34

단일 insert 시 시간은 advanced low 옵션을 사용한 경우가 가장 빨랐음
advanced high 옵션을 사용한 경우 advanced low 에 비해 2배정도 느림(oltp에서 치명적일것으로 보임)

 

 

용량 확인

SQL>
col segment_name for a30
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 = 'IX_TEST_NOCOMP2')*100, 2)||'%' per
from dba_segments
where segment_type = upper('index')
and segment_name like upper('ix_test%2');
 
SEGMENT_NAME                   SEGMENT_TYPE             BLOCKS      BYTES PER
------------------------------ -------------------- ---------- ---------- ----------
IX_TEST_ADVANCED_HIGH2         INDEX                     24576  201326592 40%
IX_TEST_ADVANCED_LOW2          INDEX                     31488  257949696 51.25%
IX_TEST_NOCOMP2                INDEX                     61440  503316480 100%

단일 insert 시에도 ADVANCED LOW옵션과 ADVANCED 옵션 모두 압축이 정상적으로 동작함
압축되지 않은 인덱스(IX_TEST_NOCOMP2)에 비해 
ADVANCED LOW 압축 인덱스(IX_TEST_ADVANCED_LOW2)는 49%,
ADVANCED HIGH 압축 인덱스(IX_TEST_ADVANCED_HIGH2)는 60%
정도로 압축됨
ADVANCED HIGH 옵션을 사용한 경우가 압축율이 더 좋음

 

 

인덱스 압축 래밸 확인

SQL>
col table_name for a30
col index_name for a30
SELECT table_name, index_name, compression
FROM dba_indexes
where index_name like 'IX_TEST%';
 
TABLE_NAME                     INDEX_NAME                     COMPRESSION
------------------------------ ------------------------------ -------------
TEST_ADVANCED_HIGH2            IX_TEST_ADVANCED_HIGH2         ADVANCED HIGH
TEST_ADVANCED_LOW2             IX_TEST_ADVANCED_LOW2          ADVANCED LOW
TEST_NOCOMP2                   IX_TEST_NOCOMP2                DISABLED

 

 

결론 : 
itas insert 의 경우
ADVANCED LOW옵션과 ADVANCED 옵션 모두 압축이 정상적으로 동작함
압축되지 않은 인덱스(IX_TEST_NOCOMP)에 비해 
APPEND 옵션을 넣지 않은 경우 ADVANCED LOW 압축 인덱스(IX_TEST_ADVANCED_LOW)는 50%,
APPEND 옵션을 넣은 경우 ADVANCED LOW 압축 인덱스(IX_TEST_ADVANCED_LOW_AP)는 66%,
APPEND 옵션을 넣지 않은 경우 ADVANCED HIGH 압축 인덱스(IX_TEST_ADVANCED_HIGH)는 56%,
APPEND 옵션을 넣은 경우 ADVANCED HIGH 압축 인덱스(IX_TEST_ADVANCED_HIGH_AP)는 69%
정도로 압축됨
각각 ADVANCED HIGH 옵션을 사용한 경우가 압축율이 더 좋고 APPEND 옵션을 넣은 경우가 압축율이 더 좋음

단일 insert 의 경우 
ADVANCED LOW옵션과 ADVANCED 옵션 모두 압축이 정상적으로 동작함
압축되지 않은 인덱스(IX_TEST_NOCOMP2)에 비해 
ADVANCED LOW 압축 인덱스(IX_TEST_ADVANCED_LOW2)는 49%,
ADVANCED HIGH 압축 인덱스(IX_TEST_ADVANCED_HIGH2)는 60%
정도로 압축됨
ADVANCED HIGH 옵션을 사용한 경우가 압축율이 더 좋음

 

 

*테스트 환경에 따라 압축률은 달라질수 있으니 참고만 하면 좋을듯 함

 

 

참조 : 

https://positivemh.tistory.com/340

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

https://positivemh.tistory.com/475

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

 

https://positivemh.tistory.com/927

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

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-8EE6F072-BADE-466B-98E6-30ABDFFCD8A9
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tablespaces.html#GUID-207D9DF2-ED00-4A40-AC7B-49A4EAD061E6
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)