- 특정 Object 에 대해서 압축 비율/ 압축 방법에 대해서 조언을 해줌
- 압축 했을 때 공간 효율 등에 대해서 예상해준다 (Insert / Load 등의 통계를 활용함)
- 테이블 compress 시 항상 관련된 인덱스는 unusable 로 빠지게 됨
* GET_COMPRESSION_RATIO : 압축 되지 않은 테이블에 대해서 예상 압축 비율을 얻고자 할 때
* GET_COMPRESSION_TYPE : 압축 유형에 대해서 추천
# dml 이력 확인
select * from DBA_TAB_MODIFICATIONS
where table_owner ='IFM_PROD'
///////////////////////////////////////////////////////////////////////////
CREATE TABLE [테이블명]
(COL1 VARCHAR(30))
COMPRESS;
CREATE UNIQUE INDEX COMPRESS_UNIQUE_EMP ON EMP(ENAME, EMPNO) COMPRESS;
ALTER TABLE [테이블명] MOVE COMPRESS;
ALTER TABLE [테이블명] MOVE PARTITION TABLESPACE [테이블스페이스명] COMPRESS;
ALTER INDEX [인덱스명] REBUILD PARTITION TABLESPACE [테이블스페이스명] COMPRESS;
[해제]
ALTER TABLE [테이블명] MOVE NOCOMPRESS;
ALTER INDEX [인덱스명] REBUILD NOCOMPRESS;
- COMPRESS(9i~) : 기본 압축 기능 제공(Default : No Compress)
- COMPRESS FOR ALL OPERATIONS(11gR1), COMPRESS FOR OLTP(11gR2) : 모든 DML에서의 압축 가능
///////////////////////////////////////////////////////////////////////////
set serveroutput on
DECLARE
blkcnt_cmp NUMBER;
blkcnt_uncmp NUMBER;
row_perblk_cmp NUMBER;
row_perblk_uncmp NUMBER;
cmp_ratio NUMBER;
comptype_str VARCHAR2(60);
BEGIN
dbms_compression.get_compression_ratio
(
'IFM_DAT', -- TBS
'IFM_PROD', -- USER
'DETAILLOG', -- TABLE
NULL,
DBMS_COMPRESSION.COMP_FOR_OLTP,
blkcnt_cmp,
blkcnt_uncmp,
row_perblk_cmp,
row_perblk_uncmp,
cmp_ratio,
comptype_str
);
dbms_output.put_line
(
'Number of blocks ' ||chr(10)||
' in compressed table: ' ||blkcnt_cmp||chr(10)||
' in uncompressed table: ' ||blkcnt_uncmp||chr(10)||
'Number of rows per block' ||chr(10)||
' in compressed table: ' ||row_perblk_cmp||chr(10)||
' in uncompressed table: ' ||row_perblk_uncmp||chr(10)||
'Test done for compression type: ' ||comptype_str||chr(10)||
'Expected compression ratio: ' ||cmp_ratio||' to 1'
);
END;
/
/////////////////////////////////////////////////////////////////////////
ALTER TABLE SCOTT.EMPTEST MOVE COMPRESS FOR OLTP;
- 11g 압축 + 후에 들어오는 DML 압축됨
ALTER TABLE SCOTT.EMPTEST MOVE COMPRESS;
- 9i 압축만 함
ALTER TABLE SCOTT.EMPTEST MOVE NOCOMPRESS;
/////////////////////////////////////////////////////////////////////////
압축 확인
* DBA_TABLES, USER_TABLES 에서 확인 가능함
* 기존의 data dictionary 에서 column 추가 됨 (COMPRESSION, COMPRESS_FOR)
* 엑사 일경우 QUERY HIGH 옵션으로 압축
ALTER TABLE ***.***** MOVE PARTITION XXXX COMPRESS FOR QUERY HIGH PARALLEL 8;
ALTER INDEX ***.***** REBUILD PARTITION XXXX PARALLEL 8;
WITH TMP_TARGET AS (SELECT /* + MATERIALIZE */
:owner OWNNAME,
:tname TABNAME,
2.0 COMPRESS_RATIO,
10 TOT_PART,
7 COMP_PART
from dual ),
WORK_TBL AS (SELECT /*+ ALL_ROWS NO_MERGE(V1) ORDERED USE_HASH(V1 a) FULL(a) PARALLEL(a 2) */
a.OWNER,
a.SEGMENT_NAME,
a.PARTITION_NAME,
a.SEGMENT_TYPE,
a.TABLESPACE_NAME,
a.BYTES,
a.BLOCKS,
a.EXTENTS,
a.INITIAL_EXTENT,
a.NEXT_EXTENT,
NVL(V1.COMPRESS_FOR, 'NONE') COMP,
V1.NUM_ROWS ANAL_ROWS,
V1.BLOCKS ANAL_BLOCKS,
decode(V1.BLOCKS, 0, 0, NULL, 0, V1.NUM_ROWS / V1.BLOCKS) ROWS_PER_BLOCK,
V1.LAST_ANALYZED
from (select *
from dba_tables b
where b.owner = (select OWNNAME
from TMP_TARGET)
and b.table_name = (select TABNAME
from TMP_TARGET)) V1,
DBA_SEGMENTS a
where a.owner = V1.owner
and a.segment_name = V1.table_name
and a.segment_type = 'TABLE'
and a.owner = (select OWNNAME
from TMP_TARGET)
and a.segment_name = (select TABNAME
from TMP_TARGET)
UNION ALL
SELECT /*+ ALL_ROWS NO_MERGE(V1) ORDERED USE_HASH(V1 a) FULL(a) PARALLEL(a 2) */
a.OWNER,
a.SEGMENT_NAME,
a.PARTITION_NAME,
a.SEGMENT_TYPE,
a.TABLESPACE_NAME,
a.BYTES,
a.BLOCKS,
a.EXTENTS,
a.INITIAL_EXTENT,
a.NEXT_EXTENT,
NVL(V1.COMPRESS_FOR, 'NONE') COMP,
V1.NUM_ROWS ANAL_ROWS,
V1.BLOCKS ANAL_BLOCKS,
decode(V1.BLOCKS, 0, 0, NULL, 0, V1.NUM_ROWS / V1.BLOCKS) ROWS_PER_BLOCK,
V1.LAST_ANALYZED
from (select *
from dba_tab_partitions b
where b.table_owner = (select OWNNAME
from TMP_TARGET)
and b.table_name = (select TABNAME
from TMP_TARGET)) V1,
DBA_SEGMENTS a
where a.owner = V1.table_owner
and a.segment_name = V1.table_name
and a.partition_name = V1.partition_name
and a.segment_type = 'TABLE PARTITION'
and a.owner = (select OWNNAME
from TMP_TARGET)
and a.segment_name = (select TABNAME
from TMP_TARGET)
UNION ALL
SELECT /*+ ALL_ROWS NO_MERGE(V1) ORDERED USE_HASH(V1 a) FULL(a) PARALLEL(a 2) */
a.OWNER,
a.SEGMENT_NAME,
a.PARTITION_NAME,
a.SEGMENT_TYPE,
a.TABLESPACE_NAME,
a.BYTES,
a.BLOCKS,
a.EXTENTS,
a.INITIAL_EXTENT,
a.NEXT_EXTENT,
NVL(V1.COMPRESS_FOR, 'NONE') COMP,
V1.NUM_ROWS ANAL_ROWS,
V1.BLOCKS ANAL_BLOCKS,
decode(V1.BLOCKS, 0, 0, NULL, 0, V1.NUM_ROWS / V1.BLOCKS) ROWS_PER_BLOCK,
V1.LAST_ANALYZED
from (select *
from dba_tab_subpartitions b
where b.table_owner = (select OWNNAME
from TMP_TARGET)
and b.table_name = (select TABNAME
from TMP_TARGET)) V1,
DBA_SEGMENTS a
where a.owner = V1.table_owner
and a.segment_name = V1.table_name
and a.partition_name = V1.subpartition_name
and a.segment_type = 'TABLE SUBPARTITION'
and a.owner = (select OWNNAME
from TMP_TARGET)
and a.segment_name = (select TABNAME
from TMP_TARGET) )
select SEGMENT_TYPE,
'NOT USED' TYPE,
sum(bytes) PART_TOT_SIZE ,
round(AVG(BYTES)) PART_AVG_SIZE,
count(*) PART_COUNT,
min(partition_name) MIN_PART,
max(partition_name) MAX_PART
from WORK_TBL
where EXTENTS = 1
GROUP BY SEGMENT_TYPE
UNION ALL
select SEGMENT_TYPE,
'COMPRESS' TYPE,
sum(bytes),
round(AVG(BYTES)) ,
count(*) PART_COUNT,
min(partition_name) MIN_PART,
max(partition_name) MAX_PART
from WORK_TBL
where COMP <> 'NONE'
and EXTENTS <> 1
GROUP BY SEGMENT_TYPE
UNION ALL
select SEGMENT_TYPE,
'UNCOMPRESS' TYPE,
sum(bytes),
round(AVG(BYTES)),
count(*) PART_COUNT,
min(partition_name) MIN_PART,
max(partition_name) MAX_PART
from WORK_TBL
where COMP = 'NONE'
and EXTENTS <> 1
GROUP BY SEGMENT_TYPE
UNION ALL
select SEGMENT_TYPE,
' -->TO COMPRESS ' || round((select COMP_PART/TOT_PART
from TMP_TARGET)*100, 1) || ' %' TYPE,
round(sum(bytes)*(((select COMP_PART
from TMP_TARGET)/(select TOT_PART
from TMP_TARGET))*(1/COMPRESS_RATIO))),
round(AVG(BYTES)*(1/COMPRESS_RATIO)),
round(count(*) *((select COMP_PART
from TMP_TARGET)/(select TOT_PART
from TMP_TARGET))) PART_COUNT,
min(partition_name) MIN_PART,
max(partition_name) MAX_PART
from WORK_TBL,
TMP_TARGET
where COMP = 'NONE'
and EXTENTS <> 1
GROUP BY SEGMENT_TYPE,
COMPRESS_RATIO
UNION ALL
select SEGMENT_TYPE,
' -->NO COMPRESS ' || round((select (TOT_PART - COMP_PART)/TOT_PART
from TMP_TARGET)*100, 1) || ' %' TYPE,
round(sum(bytes)*((select TOT_PART - COMP_PART
from TMP_TARGET)/(select TOT_PART
from TMP_TARGET))),
round(AVG(BYTES)),
round(count(*) *((select TOT_PART - COMP_PART
from TMP_TARGET)/(select TOT_PART
from TMP_TARGET))) PART_COUNT,
min(partition_name) MIN_PART,
max(partition_name) MAX_PART
from WORK_TBL
where COMP = 'NONE'
and EXTENTS <> 1
GROUP BY SEGMENT_TYPE
ORDER BY 1,
2,
3