compress 옵션

오라클의 compress 기능
 1. 특성
   - 9iR2에 소개됨.
   - table, index, mview, partition table의 각 partition 에 적용할 수 있음.
   - alter table ~ move 명령어를 사용하면 해당 오브젝트 전체에 lock이 걸림.
   - 압축이 되기 때문에 스토리지 비용절감/IO개선 효과가 있음.
   - insert, delete 에서는 성능차이가 거의 발생하지 않지만, update시에는 많이 발생함.
   - 데이터가 많이 변하고 많은 세션이 있는 oltp에서는 부적합하고,
     데이터가 변화가 거의 없고, 조회성 업무가 많은 DW 환경에서 적합.
   - 방법
     1) direct sql*loader
     2) CTAS
     3) parallel insert
     4) direct insert /*+ append */
 
   - 명령어
     1) 압축
       : alter table <테이블명> move compress;
       : alter table <테이블명> move    partition tablespace <테이블스페이스명> compress;
       : alter index <인덱스명> rebuild partition tablespace <테이블스페이스명> compress;
     2) 해제
       : alter table <테이블명> move nocompress;
       : alter index <인덱스명> rebuild nocompress
    
 2. 테스트
   - 압축율은 어떻게 될까?
     1) 같은 데이터가 많이 있을 경우
    
       select * from v$version where rownum = 1;
      
       BANNER
       ----------------------------------------------------------------
       Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

       drop table t1 purge;
       create table t1 (col1 number, col2 varchar2(100), col3 varchar2(100));
      
       insert into t1
       select level,
              'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',
              'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
       from dual
       connect by level <= 100000;
      
       commit;
      
       create table com_t1
       as
       select * from t1 where 1=2;
      
       alter table com_t1 move compress;
        => 그냥 alter table com_t1 compress; 는 안됨.
      
       insert /*+ full append */ into com_t1
       select * from t1;
      
       commit;
      
       select segment_name, bytes
       from dba_segments
       where segment_name in ('T1','COM_T1');
      
       SEGMENT_NAME       BYTES
       ------------------------------ ----------
       T1     17825792
       COM_T1     15728640  
      
     2) 같은 데이터가 거의 없을 경우
    
   
       drop table t2 purge;
       create table t2 (col1 number, col2 varchar2(100), col3 varchar2(100));
      
       insert into t2
       select level,
              'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',
              'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
       from dual
       connect by level <= 25000
       union all
       select level,
              'cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc',
              'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd'
       from dual
       connect by level <= 25000
       union all
       select level,
              'eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee',
              'ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff'
       from dual
       connect by level <= 25000
       union all
       select level,
              'gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg',
              'ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff'
       from dual
       connect by level <= 25000;
      
       commit;
      
       create table com_t2
       as
       select * from t2 where 1=2;
      
       alter table com_t2 move compress;
      
       insert /*+ append */ into com_t2
       select * from t2;
      
       commit;
      
       select segment_name, bytes
       from dba_segments
       where segment_name in ('T2','COM_T2');
    
            SEGMENT_NAME       BYTES
       ------------------------------ ----------
       T2     17825792
       COM_T2     15728640

       => 똑같네.ㅠㅠ
   
      ※ 만약에 append 힌트를 안썼을 경우는?
         => 결과는 같음. 뭐니??
        insert into com_t2
        select * from t2;
   
            SEGMENT_NAME       BYTES
        ------------------------------ ----------
        T2     17825792
        COM_T2     15728640
   
      3) 테이블생성시에 compress 옵션을 주게 되면??
     
        create table init_com_t2
        compress
        as
        select * from t1;
      
        select segment_name, bytes
        from dba_segments
        where segment_name in ('T2','COM_T2','INIT_COM_T2');
      
        SEGMENT_NAME       BYTES
        ------------------------------ ----------
        INIT_COM_T2     2097152
        T2     17825792
        COM_T2     15728640
      
        => 훨씬 압축율이 좋다.
           88%, 12%이다.
  
 ※ 참고사이트
    http://ej5811.blog.me/80112840894
    http://blog.naver.com/conifer7?Redirect=Log&logNo=30093121932