(Oracle) Compressed Table

http://www.gurubee.net/display/DBSTUDY/Compressed+Table

 

Compressed Table

개요

• Compression의 원리
  • Data Segment Compression 또는 heap-organized block-level compression : 한 block내에서 복원되어야 하는 정보는 그 Block내에 모두 존재해야 함
    
    테이블
    
    
    압축 전 : block 내 저장 형태
    
    
    압축 후 : block 내 저장 형태
    
    
    

    Symbol Table : column 길이와 중복값의 정도에 따라 모든 중복값이 저장 중복 컬럼 외의 컬럼들은 컬럼의 순서가 해당 block내에서 재정렬 될 수 있음

    
    
    

• 특징
  1. 물리적인 저장공간 절약
  2. DB2와 같은 타 DBMS에서는 이미 사용되고 있는 방법
  3. Oracle 9i Release2 new feature
  4. TABLE, TABLE Partition, Materialized View에서 사용 가능 (Tablespace 단위로 지정 가능)
  5. Block 단위로 I/O를 수행하므로 물리적 I/O 감소
  6. Lossless dictionary-based 알고리즘 사용 : block내 중복값 제거하는 압축방법 사용
  7. Compression 및 Decompression : ROW 단위 수행. Row level locking은 유효
     
     
• 장점
  1. DW : QUERY ELASPED TIME 을 10~15% 정도 개선
     cf) OLTP : 수행속도가 오히려 약간 늦어지는 걸로 나타남
  2. 백업 및 복구 시간 단축 :압축함으로써 저장 공간을 절약할 수 있기 때문에 백업 및 복구에 소요되는 시간을 줄일 수 있다.
     
     
• Compression Ratio(압축율)
  1. 데이터의 분포도에 따라 다름 : DW환경에서는 2:1, 4:1, 12:1
  2. 온라인 30~80%, DW 400%까지도 가능(데이터 분포도에 따라 다름)
  3. 각 Block의 중복값을 제거하는 알고리즘을 사용 : 데이터 로드시에 Cardinality가 낮은 값으로 정렬해서 data를 Loading하는 방법이 좋음
  4. DW에서 Materialized View : 대부분 Group by에 의해 정렬된 값을 생성하는 경우가 많으므로 높은 Compression Ratio 유지 가능
  5. block size가 클수록 한 Block내에 중복값이 많이 포함 될 가능성 있음
  6. PCTFREE(Storage Parameter)와 Compression Ratio
     1. PCTFREE 낮음 - Compression Ratio 높음 : Block내에 많은 양의 데이터 저장 가능하므로
     2. PCTFREE = 0 으로 설정하는 경우
        • UPDATE 작업이 거의 없는 Compressed Table
        • table creation : compress option을 사용하여 default PCTFREE = 0 지정
  7. 생성 시간 > 압축 시간 : 약 2배 이상의 시간이 필요
  8. 50%의 Compress ratio
     1. SELECT : 속도 TEST시 거의 차이가 나타나지 않음
     2. Update : 40~70% 정도 더 낮은 성능
        압축된 Table의 update시 더 복잡한 operation 과정이 필요하고 PCTFREE등의 Storage Parameter등의 영향 때문임
     3. Delete : 평균 30%정도의 성능 향상 됨
        data의 압축으로 logging data의 양도 줄어들게 되고 row의 길이가 줄어들게 되어 1 Block내에 더 많은 Row를 포함하는 등의 영향 때문임
        
        
• 고려사항
  1. 기존 테이블에 compress option 지정 : 테이블 전체에 Exclusive Lock 이 걸림
     
     

Compression 성능

• Compression 대상
  1. Table, Materialized View, Partition table의 개별 Partition별로 Compress option지정 가능
  2. Tablespace level 지정 : tablespace내에 생성되는 모든 Table들은 default로 Compress option 적용
  3. Table의 Compress option : 변경 가능하나 이후에 입력되는 데이터만 Compression 적용 대상임
  4. Compress 적용 테이터 타입 : 대부분의 데이터 타입은 적용가능하나 LOB, LOB, CLOB등은 제외
  5. bulk insert, bulk load시 Compresion이 발생되는 시점
     1. Direct Path SQL*Loader 사용
     2. Create Table as SELECT(CTAS) 문장 사용
     3. Parallel INSERT (또는 append hint를 통한 Serial INSERT) 문장 사용
        
        
• 사용법
  1. 9iR2 : compress table 에 대해서 컬럼 추가나 삭제가 지원 NO!!

     9iR2에서 칼럼 삭제 방법 create table new_테이블명 COMPRESSE as select t.*, <new column here> from old_테이블명 t; drop old_테이블명; rename new_테이블명 to old_테이블명;

  2. 10gR2 : compress table 에 컬럼 추가 가능(삭제 지원 NO!!)

     10gR2에서 칼럼 삭제 방법 alter table 테이블명 set unused column x; alter table 테이블명 drop unused columns;

  3. 사용 가능한 경우

     create table as select insert /*+ append */ sqlldr direct=y alter table move ex) ALTER TABLE TABLE_NAME MOVE COMPRESS;

  4. compression 적용 여부 확인
     1. partitioned table

        SELECT TABLE_NAME, PARTITION_NAME, COMPRESSION 
           FROM DBA_TAB_PARTITIONS
           WHERE TABLE_NAME LIKE UPPER(:TAB_NAME)||'%';

     2. non-partitioned table

        SELECT E.OWNER, E.SEGMENT_NAME, E.SEGMENT_TYPE, S.SPARE1,
                 DECODE(S.SPARE1,1,'NORMAL',6145,'COMPRESSED',4097,'COMP->NOCOMP','OTHER') COMP_TYPE
           FROM SYS.SEG$ S, DBA_EXTENTS E
           WHERE E.SEGMENT_NAME LIKE UPPER(:seg_name)||'%'   
             AND E.SEGMENT_TYPE = 'TABLE'
             AND E.EXTENT_ID    = 0
             AND E.BLOCK_ID     = S.BLOCK#
             AND E.FILE_ID      = S.FILE#;

        
        
        

• Compression/non-Compress Table 성능비교
  1. 물리적인 compress ratio : 데이터 분포도에 따라 다름
  2. 높은 Compression ratio가 미치는 영향
     1. Data file size나 Redo log file Size가 줄어듦
     2. Backup & Recovery등 유지보수 효과 높음
     3. Database buffer cache의 Hit Ratio 향상에 영향 미침
     4. 물리적인 DISK I/O 김소
  3. DW vs OLTP

     DW	OLTP
     Cardinality 낮음	Transaction 데이터 : Cardinality가 높아 중복 값의 비중이 낮음
     Compression Option 사용 권장	사전 Sampling을 통하여 최소 50%이상의 압축율을 보이는지 여부와 DML 사용정도등을 조사 : DML의 요구가 많지 않은 집계성 Table에 사용할 것 권장

  4. Bug

     Compressed Table Bug note 258597.1 Moving And Compressing A Table Subpartition Fails Ora-14160 8.1.7.0 bugno 1250521 COMPRESSED KEY INDEXes can become logically corrupt signalling one of a number of errors (eg: ORA-600 6101, ORA-600 6121 or similar 8.1.7.0 bugno 1179002 While processing an insert of a prefix key in a compressed index a data structure is allocated on a local stack. Do not compress the index. 8.1.7.0 bugno 1101318 Analyze validate structure of a key compressed index shows wrong values for DISTINCT_KEYS, ROWS_PER_KEY, BLKS_GETS_PER_ACCESS in index_stats. statspack note228913.1 If the tablespace contains indexes, another option is to compress the indexes so that they require less space and hence, less IO. 8, 8i, note 212955.1 Compiling a Package After Running two or More ADD COLUMN Statements Fails With ORA-04020, Workarounds are droppinng/recreating the Update Trigger or compress alter table statements to a single statement. note 179380.1 Bad indexing and query performance on Intermedia Text indexes, Note1: The compress clause is much less important to ensuring good performance than the cache clause for the R_TABLE_CLAUSE. The cache clause will give the greatest performance improvement by far. 8, 8i, 9i, 10g bug 2421054.8 Bug 2421054 ENH: Allow ALTER TABLE to ADD/DROP columns for tables using COMPRESS feature 9.2.0.3 note 256236.1 ORA-14646 with using bitmap indexes on partitioned compressed tables 10g r2 이하 모든 버젼, Bug 4177800 - Update on a compress table to set a column to NULL can corrupt the table 8.1.7.4 note 272373.1 After Merge Of Two Table Partitions, Index Compression Flag is reset. 9i r2 이상 10g r2 이하, Bug 2856059 OERIKCBGTCR_1 selecting from COMPRESSED IOT after UPGRADE from 8.1.7 to 9.2 9i r1, r2 note 245918.1 ALERT: Problems accessing IOTs after upgrade to 9.2 9206에서 fix , bugno 3262424 ALTER TABLE NOCOMPRESS does not work for partitioned tables 9206에서 fix , bug 3347963 OERI 6120 during compressed IOT insert 9206에서 fix , bug 3629771 Wrong results possible from COMPRESS KEY INDEX SCAN 9206에서 fix bug OERI 6120 during compressed IOT insert 9206에서 fix bug 2481414 ALTER TABLESPACE ... DEFAULT COMPRESS reports ORA-10616

참고문서

1. OTN 포럼 - Compress를 실제 사용하고 있는 사용자의 버그가 소개 됨
2. OTN 포럼 - 민연홍님께서 compress에 대한 의견 및 메타링크에 있는 bug 목록 소개
3. 엔코아 - 로그인해야하지만, compressed Table에 대해서 개념 및 테스트 실행 결과를 올린 칼럼
(http://www.en-core.com/bin/main/module/solution/view.asp?solution_code=&searchString=compress&column=TITLE&article_id=13333&state=view&board_id=solution&page_num=1&group_id=13333&direction=n&step=0)

문서에 대하여

• 최초작성자 : 박혜은
• 최초작성일 : 2009년 11월 26일
• 이 문서에 있는 테스트 결과는 DBMS버전과 구성된 환경에 따라 다를 수 있습니다.

문서정보

• 이 문서는 오라클클럽에서 작성하였습니다.
• 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• 출처 : http://www.gurubee.net/display/DBSTUDY/Compressed+Table?
• 오라클클럽 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.