— Table Compression

https://oracle-base.com/articles/11g/dbms_compression-11gr2
https://www.oracle.com/us/assets/lad-2015-ses16380-pedregal-2604876.pdf
http://www.bestremotedba.com/2017/01/25/how-to-advance-compression-12c/
https://www.morganslibrary.org/reference/compression.html

--- MOS Notes
 - Advanced Compression Master Note(Doc ID 1223705.1)
 – How to compress a table that is online(Doc ID 1353967.1)
 – Advanced Compression critical patches(Doc ID 1061366.1)
 – Redo Transport compression with Data Guard (Doc ID 729551.1)
 – How to see if rows are compressed in a table(Doc ID 1477918.1)

--- Tested on

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 PL/SQL Release 12.2.0.1.0 - Production
 CORE 12.2.0.1.0 Production
 TNS for Linux: Version 12.2.0.1.0 - Production
 NLSRTL Version 12.2.0.1.0 - Production

— Table: cmagno.testcomp

create tablespace ts_2011 datafile '/opt/oracle/oradata/db01/DB01/datafile/ts_2011.dbf' size 10m autoextend on next 10m maxsize 1g;

 create tablespace ts_2012 datafile '/opt/oracle/oradata/db01/DB01/datafile/ts_2012.dbf' size 10m autoextend on next 10m maxsize 1g;

 create tablespace ts_2013 datafile '/opt/oracle/oradata/db01/DB01/datafile/ts_2013.dbf' size 10m autoextend on next 10m maxsize 1g;

 create tablespace ts_2014 datafile '/opt/oracle/oradata/db01/DB01/datafile/ts_2014.dbf' size 10m autoextend on next 10m maxsize 1g;

 create tablespace ts_2015 datafile '/opt/oracle/oradata/db01/DB01/datafile/ts_2015.dbf' size 10m autoextend on next 10m maxsize 1g;

 create tablespace ts_2016 datafile '/opt/oracle/oradata/db01/DB01/datafile/ts_2016.dbf' size 10m autoextend on next 10m maxsize 1g;

 create tablespace ts_2017 datafile '/opt/oracle/oradata/db01/DB01/datafile/ts_2017.dbf' size 10m autoextend on next 10m maxsize 1g;

 create tablespace ts_2018 datafile '/opt/oracle/oradata/db01/DB01/datafile/ts_2018.dbf' size 10m autoextend on next 10m maxsize 1g;

 create tablespace ts_2019 datafile '/opt/oracle/oradata/db01/DB01/datafile/ts_2019.dbf' size 10m autoextend on next 10m maxsize 1g;

CREATE TABLE CMAGNO.TESTCOMP
 (
 NUM NUMBER
 , DATE_INS DATE
 , TEXT VARCHAR2(200)
 )
 TABLESPACE "TS_TESTE"
 PARTITION BY RANGE (DATE_INS) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 (
 PARTITION P2011 VALUES LESS THAN (TO_DATE('01-JAN-2012','dd-MON-yyyy')) TABLESPACE "TS_2011"
 , PARTITION P2012 VALUES LESS THAN (TO_DATE('01-JAN-2013','dd-MON-yyyy')) TABLESPACE "TS_2012"
 , PARTITION P2013 VALUES LESS THAN (TO_DATE('01-JAN-2014','dd-MON-yyyy')) TABLESPACE "TS_2013"
 , PARTITION P2014 VALUES LESS THAN (TO_DATE('01-JAN-2015','dd-MON-yyyy')) TABLESPACE "TS_2014"
 , PARTITION P2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','dd-MON-yyyy')) TABLESPACE "TS_2015"
 , PARTITION P2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','dd-MON-yyyy')) TABLESPACE "TS_2016"
 , PARTITION P2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','dd-MON-yyyy')) TABLESPACE "TS_2017"
 , PARTITION P2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','dd-MON-yyyy')) TABLESPACE "TS_2018"
 , PARTITION P2019 VALUES LESS THAN (TO_DATE('01-JAN-2020','dd-MON-yyyy')) TABLESPACE "TS_2019"
 );

— Loading data

declare
 varDate date;
 varNum number;
 begin

varDate := to_date('01/01/2011','DD/MM/YYYY');

while varDate != trunc(sysdate) loop
   select hsecs into varNUm from v$ timer;
   insert into cmagno.TESTCOMP values (varNum, varDate, 'Data for :' || to_char(varDate,'DD/MM/YYYY'));
   varDate := vardate +(1/24);
end loop;
 commit;
dbms_stats.gather_table_stats('CMAGNO','TESTCOMP');
end;
 /

--- Execute some times the command below in order to have more data.
 insert into cmagno.TESTCOMP select * from cmagno.TESTCOMP;
 commit;
 exec dbms_stats.gather_table_stats('CMAGNO','TESTCOMP');

— Checking the current status

col partition_name format a15;
 col tablespace_name format a15;

select
 a.partition_name, a.tablespace_name, a.compression, a.compress_for , round(sum(a.num_rows / a.blocks),0) rows_per_block,
 sum(a.num_rows) num_rows, sum(a.blocks) blocks , sum(b.bytes) /(1024*1024) mb
 from dba_tab_partitions a, dba_segments b
 where
 a.table_name = 'TESTCOMP' and
 a.partition_name = b.partition_name and
 a.table_name = b.segment_name
 group by a.partition_name, a.tablespace_name, a.compression, a.compress_for
 order by 1; 

PARTITION_NAME TABLESPACE_NAME COMPRESS COMPRESS_FOR NUM_ROWS BLOCKS MB --------------- --------------- -------- ----------------- ------------ -------------------- -------------------- P2011 TS_2011 DISABLED 10465280 58054 456 P2012 TS_2012 DISABLED 10493952 58054 456 P2013 TS_2013 DISABLED 10465280 58054 456 P2014 TS_2014 DISABLED 10465280 58054 456 P2015 TS_2015 DISABLED 10465280 58054 456 P2016 TS_2016 DISABLED 10493952 58054 456 P2017 TS_2017 DISABLED 10465280 58054 456 P2018 TS_2018 DISABLED 10465280 58054 456 P2019 TS_2019 DISABLED 1462272 9094 72

— Checking the Compress ratio

SET SERVEROUTPUT ON
 DECLARE
 l_blkcnt_cmp PLS_INTEGER;
 l_blkcnt_uncmp PLS_INTEGER;
 l_row_cmp PLS_INTEGER;
 l_row_uncmp PLS_INTEGER;
 l_cmp_ratio NUMBER;
 l_comptype_str VARCHAR2(32767);
 BEGIN
 DBMS_COMPRESSION.get_compression_ratio (
 scratchtbsname => 'TS_2011',
 ownname => 'CMAGNO',
 objname => 'TESTCOMP',
 subobjname => 'P2011',
 comptype => DBMS_COMPRESSION.comp_advanced,
 blkcnt_cmp => l_blkcnt_cmp,
 blkcnt_uncmp => l_blkcnt_uncmp,
 row_cmp => l_row_cmp,
 row_uncmp => l_row_uncmp,
 cmp_ratio => l_cmp_ratio,
 comptype_str => l_comptype_str,
 subset_numrows => DBMS_COMPRESSION.comp_ratio_allrows,
 objtype => DBMS_COMPRESSION.objtype_table
 );

DBMS_OUTPUT.put_line('Number of blocks used (compressed) : ' || l_blkcnt_cmp);
 DBMS_OUTPUT.put_line('Number of blocks used (uncompressed) : ' || l_blkcnt_uncmp);
 DBMS_OUTPUT.put_line('Number of rows in a block (compressed) : ' || l_row_cmp);
 DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' || l_row_uncmp);
 DBMS_OUTPUT.put_line('Compression ratio : ' || l_cmp_ratio);
 DBMS_OUTPUT.put_line('Compression type : ' || l_comptype_str);
 END;
 /

Number of blocks used (compressed) : 751
 Number of blocks used (uncompressed) : 1577
 Number of rows in a block (compressed) : 378
 Number of rows in a block (uncompressed) : 179
 Compression ratio : 2
 Compression type : "Compress Advanced"

— Compression Type:
https://docs.oracle.com/database/121/ARPLS/d_compress.htm#ARPLS65600

— Compressing:

Basic Compression -> Read only tables and partitions in Data Warehouse environments or “inactive” data partitions in OLTP environments
Advanced Row Compression -> Active tables and partitions in OLTP and Data Warehouse environments
Advanced LOB Compression and Deduplication -> Non-relational data in OLTP and Data Warehouse environments

alter table cmagno.TESTCOMP compress partitionfor oltp;

-- All OPERATIONS
 ALTER TABLE cmagno.TESTCOMP move PARTITION P2011 COMPRESS FOR ALL OPERATIONS;

-- OLTP
 ALTER TABLE cmagno.TESTCOMP move PARTITION P2012 COMPRESS FOR OLTP;

-- Basic
 ALTER TABLE cmagno.TESTCOMP move PARTITION P2013 COMPRESS ;

-- Compress Advanced
 ALTER TABLE cmagno.TESTCOMP move PARTITION P2014 ROW STORE COMPRESS ADVANCED;

— Checking the Partitions

exec dbms_stats.gather_table_stats(‘CMAGNO’,’TESTCOMP’);

select
 a.partition_name, a.tablespace_name, a.compression, a.compress_for , round(sum(a.num_rows / a.blocks),0) rows_per_block,
 sum(a.num_rows) num_rows, sum(a.blocks) blocks , sum(b.bytes) /(1024*1024) mb
 from dba_tab_partitions a, dba_segments b
 where
 a.table_name = 'TESTCOMP' and
 a.partition_name = b.partition_name and
 a.table_name = b.segment_name
 group by a.partition_name, a.tablespace_name, a.compression, a.compress_for
 order by 1; 

PARTITION_NAME TABLESPACE_NAME COMPRESS COMPRESS_FOR ROWS_PER_BLOCK NUM_ROWS BLOCKS MB --------------- --------------- -------- -------------- -------------------- -------------------- -------------------- -------------------- P2011 TS_2011 ENABLED ADVANCED 376 10465280 27858 224 P2012 TS_2012 ENABLED ADVANCED 374 10493952 28081 224 P2013 TS_2013 ENABLED BASIC 416 10465280 25168 200 P2014 TS_2014 ENABLED ADVANCED 374 10465280 27983 224 P2015 TS_2015 DISABLED 180 10465280 58054 456 P2016 TS_2016 DISABLED 181 10493952 58054 456 P2017 TS_2017 DISABLED 180 10465280 58054 456 P2018 TS_2018 DISABLED 180 10465280 58054 456 P2019 TS_2019 DISABLED 161 1462272 9094 72

— Identify the partition Type

Checking for
*** P2011
ROWID COMPRESSION_TYPE
—————— —————————
AAAbNQAACAAAOSSAAA COMP_ADVANCED
AAAbNQAACAAAOSSAAB COMP_ADVANCED
AAAbNQAACAAAOSSAAC COMP_ADVANCED

*** P2012
ROWID COMPRESSION_TYPE
—————— —————————
AAAbNRAAIAAAOSSAAA COMP_ADVANCED
AAAbNRAAIAAAOSSAAB COMP_ADVANCED
AAAbNRAAIAAAOSSAAC COMP_ADVANCED

*** P2013
ROWID COMPRESSION_TYPE
—————— —————————
AAAbNSAAJAAAOSSAAA COMP_BASIC
AAAbNSAAJAAAOSSAAB COMP_BASIC
AAAbNSAAJAAAOSSAAC COMP_BASIC
*** P2014
ROWID COMPRESSION_TYPE
—————— —————————
AAAbNTAAKAAAOSSAAA COMP_ADVANCED
AAAbNTAAKAAAOSSAAB COMP_ADVANCED
AAAbNTAAKAAAOSSAAC COMP_ADVANCED

SELECT rowid,
 CASE DBMS_COMPRESSION.get_compression_type ('CMAGNO', 'TESTCOMP', rowid, 'P2014')
 WHEN 1 THEN 'COMP_NOCOMPRESS'
 WHEN 2 THEN 'COMP_ADVANCED'
 WHEN 4 THEN 'COMP_QUERY_HIGH'
 WHEN 8 THEN 'COMP_QUERY_LOW'
 WHEN 16 THEN 'COMP_ARCHIVE_HIGH'
 WHEN 32 THEN 'COMP_ARCHIVE_LOW'
 WHEN 64 THEN 'COMP_BLOCK'
 WHEN 128 THEN 'COMP_LOB_HIGH'
 WHEN 256 THEN 'COMP_LOB_MEDIUM'
 WHEN 512 THEN 'COMP_LOB_LOW'
 WHEN 1024 THEN 'COMP_INDEX_ADVANCED_HIGH'
 WHEN 2048 THEN 'COMP_INDEX_ADVANCED_LOW'
 WHEN 1000 THEN 'COMP_RATIO_LOB_MINROWS'
 WHEN 4096 THEN 'COMP_BASIC'
 WHEN 5000 THEN 'COMP_RATIO_LOB_MAXROWS'
 WHEN 8192 THEN 'COMP_INMEMORY_NOCOMPRESS'
 WHEN 16384 THEN 'COMP_INMEMORY_DML'
 WHEN 32768 THEN 'COMP_INMEMORY_QUERY_LOW'
 WHEN 65536 THEN 'COMP_INMEMORY_QUERY_HIGH'
 WHEN 32768 THEN 'COMP_INMEMORY_CAPACITY_LOW'
 WHEN 65536 THEN 'COMP_INMEMORY_CAPACITY_HIGH'
 END AS compression_type
 FROM cmagno.TESTCOMP PARTITION (P2014)
 WHERE rownum <= 3;

Conclusions:

Carlos Magno – EzDBA – BLOG