Oracle database 19c Compression Types

  

We  had multiple  requirement for Compression hence documenting handy notes  that can help myself and others . 

To   automate    compression   we have  feature of Oracle Automatic Data Optimisation  which we will discuss later 

==> Compression types : 

We can know  type of compression from  below sql 

SQL> select name, currently_used from dba_feature_usage_statistics where lower(name) like ‘%compress%’; 

• BASIC compression, introduced in Oracle 8 already and only recommended for Data Warehouse
• OLTP compression, introduced in Oracle 11 and recommended for OLTP Databases as well
• QUERY LOW compression (Exadata only), recommended for Data Warehouse with Load Time as a critical factor
• QUERY HIGH compression (Exadata only), recommended for Data Warehouse with focus on Space Saving
• ARCHIVE LOW compression (Exadata only), recommended for Archival Data with Load Time as a critical factor
• ARCHIVE HIGH compression (Exadata only), recommended for Archival Data with maximum Space Saving

Basic Table Compression: 

This is a free data compression capability included with the Enterprise Edition. Basic Compression compresses data that is 

loaded using bulk load operations (direct path), but does not compress data added or updated through conventional DML operations (INSERT or UPDATE). 

To compress data added or updated over time, you need to re-compress the table or partition.

Usage: Basic Table Compression is not intended for OLTP applications. It is best suited for data warehouse applications 

where data is loaded using bulk load operations and is rarely modified.

-- Creating a table with basic compression

CREATE TABLE employees (

    employee_id NUMBER PRIMARY KEY,

    first_name VARCHAR2(50),

    last_name VARCHAR2(50),

    email VARCHAR2(100),

    hire_date DATE

) COMPRESS BASIC;

-- Altering an existing table to enable basic compression

ALTER TABLE employees COMPRESS BASIC;

Advanced Row Compression

A new feature in Oracle Database 11g Enterprise Edition allows to compress transaction-intensive online transaction processing (OLTP) databases requires a license for “Advanced Compression Option (ACO)”

Row Compression is another method to reduce storage space by compressing individual rows of data within a table. 

This technique can be particularly effective for tables with large amounts of repetitive data.

Advanced Row Compression  feature of Advanced Compression maintains data compression during all types of data manipulation operations,  including conventional DML such as INSERT and UPDATE. It uses a compression algorithm specifically designed to eliminate duplicate values 

within a database block, even across multiple columns.

Usage: Advanced Row Compression is suitable for both OLTP and Data Warehouse applications.

-- Creating a table with advanced row compression

CREATE TABLE sales (

    sale_id NUMBER PRIMARY KEY,

    sale_date DATE,

    amount NUMBER

) COMPRESS FOR OLTP;

-- Altering an existing table to enable advanced row compression

ALTER TABLE sales COMPRESS FOR OLTP;

HCC — Data Warehouse and Archive options:

Hybrid Columnar Compression is an enabling technology for both HCC Warehouse Query Compression and HCC Archive Compression.

We can implement HCC at the table, partition & tablespace level.

✔10x average storage saving

✔10x scan I/O reduced

 HCC delivers (far) better Compression Ratios than the Block Compression methods. Load Time increases from QUERY LOW (best) over QUERY HIGH and ARCHIVE LOW (both moderate) to ARCHIVE HIGH (longest Load Time). 

Query Performance decreases similar from QUERY LOW (best) to ARCHIVE HIGH (longest Query Time). All HCC methods except ARCHIVE HIGH delivered better Query Performance than uncompressed, though. 

In short: HCC is most likely able to save large amounts of space in your Data Warehouse without decreasing Query Performance much if at all. It will probably compress your Archival Data significantly.

Table compression methods used in Exadata

> QUERY LOW compression, for DW with Load Time as a critical factor. ( DW with load time critical factor Fastest Load Time) 

> QUERY HIGH compression, for DW with focus on Space Saving.  ( DW with focus on Space Saving) 

> ARCHIVE LOW compression, for Archival Data with Load Time as a critical factor.  (  Archival Data with Load Time Critical factor ) 

> ARCHIVE HIGH compression, for Archival Data with maximum Space Saving. ( Archival Data with maximum space saving. ) 

=> What can we compress 

Table : 

FOR ALL OPERATIONS

Index : 

compress advanced high

Column Store Compression

Hybrid Columnar Compression (HCC):

Lob : 

COMPRESS high

Tablespace : 

COMPRESS ADVANCED HIGH

COMPRESS FOR ALL OPERATIONS

Exdata : 

Hybrid Columnar Compression

Rman : 

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default 

Data Pump Compression

expdp … COMPRESSION=ALL

expdp … COMPRESSION=DATA_ONLY

==> Sample commands for  Creating Compression object 

  

CREATE TABLE t_hcc_query_low    COMPRESS FOR QUERY LOW AS SELECT * FROM dba_source;

  CREATE TABLE t_hcc_query_high   COMPRESS FOR QUERY HIGH AS SELECT * FROM dba_source;

  CREATE TABLE t_hcc_archive_low  COMPRESS FOR ARCHIVE LOW AS SELECT * FROM dba_source;

  CREATE TABLE t_hcc_archive_high COMPRESS FOR ARCHIVE HIGH AS SELECT * FROM dba_source;

create table tbl_lob_compress (id number,lob_data clob) 

   lob(lob_data) store as securefile (tablespace example compress

create table sawr$session_stats (

       snapid                       number   

     , snaptime                     date     

     , audsid                       number   

     , sid                          number   

     , serial#                      number   

     , statistic#                   number   

     , value                        number   

     , constraint sawr$session_stats_pk primary key (

         snapid,

         snaptime,

         audsid,

         sid,

         serial#,

         statistic#

     )

)

organization index compress;

select TABLE_NAME,LOB_NAME,PARTITION_NAME,COMPRESSION  from dba_lob_partitions where tablespace_name='ABC';

select TABLE_NAME,COMPRESSION, COLUMN_NAME  from dba_lobs where tablespace_name='ABC'; 

  alter table test move partition part2 compress online ;

ALTER TABLE paratab_dop_8 MOVE COMPRESS for query high parallel 8;

==>  Creating  Compressed  Tablespace :

 CREATE TABLESPACE "HIST_DATA2" DATAFILE

  'HIST_DATA2_1.dbf' SIZE 1g autoextend on next 1m

  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT

 COMPRESS FOR ALL OPERATIONS  SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE "HIST_INDEX2" DATAFILE

  'HIST_INDEX2.dbf' SIZE 1g autoextend on 

  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT

 COMPRESS ADVANCED HIGH   SEGMENT SPACE MANAGEMENT AUTO;

sELECT TABLESPACE_NAME, DEF_TAB_COMPRESSION, COMPRESS_FOR ,def_index_compression ,index_compress_for FROM DBA_TABLESPACES  ;

==>   Moving objects to new compressed  tablespace 

If we are planing to  only compress  existing  object and not move we need  use  below order for  Parttion lob table 

1) Compress Lob partition

2) Compress table

3) Compress table partition

 

https://www.appservgrid.com/documentation111/docs/rdbms11gr2/appdev.112/e18294/loe.htm

https://docs.oracle.com/en/database/oracle/oracle-database/19/adlob/using-oracle-LOBs-storage.html#GUID-3F76D891-BAE3-463B-9109-0D7674AF8DA9

Note   :  For partition object we  need to update attribute for both partition and parent min object 

--> Moving Tables 

set line 400 

col TABLE_OWNER format a20 

col owner format a20

col segment_name format a20

col tablespace_name format a20 

col TABLE_NAME  format a60

col segment_type format a20

select 'alter table '||owner||'.'||segment_name||' move tablespace  HIST_DATA2 COMPRESS FOR ALL OPERATIONS ;'  from  dba_segments where tablespace_name='HIST_DATA'  and segment_type='TABLE' AND SEGMENT_NAME NOT LIKE '%LOB%'; 

SELECT 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME ||' MOVE PARTITION '||partition_name|| ' TABLESPACE  HIST_DATA2  COMPRESS FOR ALL OPERATIONS ; '  FROM DBA_TAB_PARTITIONS  WHERE tablespace_name='HIST_DATA'; 

select 'ALTER TABLE '|| owner||'.'||table_name||' modify default attributes   ( tablespace HIST_DATA2 ) ;'   from dba_tables where TABLESPACE_NAME = 'HIST_DATA' and Partitioned='YES';

select 'ALTER TABLE '|| owner||'.'||table_name||'MODIFY DEFAULT ATTRIBUTES FOR PARTITION '||partition_name|| 'TABLESPACE  HIST_DATA; ' FROM DBA_TAB_PARTITIONS  WHERE tablespace_name='HIST_DATA'; 

set line 400

col table_name format a20

SELECT owner , TABLE_NAME, COMPRESSION, COMPRESS_FOR ,Partitioned  FROM dba_TABLES  WHERE TABLE_NAME in ( select segment_name from dba_segments where tablespace_name='HIST_DATA2') ; 

SELECT owner , TABLE_NAME, COMPRESSION, COMPRESS_FOR ,Partitioned  FROM dba_TABLES  WHERE tablespace_name='HIST_DATA2'  ;

SELECT table_owner , TABLE_NAME, COMPRESSION, COMPRESS_FOR   FROM dba_TAB_partitions  WHERE tablespace_name='HIST_DATA2'  ;

Sample :

ALTER TABLE sales  MOVE PARTITION sales_q1_1998 TABLESPACE ts_arch_q1_1998 COMPRESS;

ALTER TABLE sales   MOVE PARTITION sales_q1_1998 TABLESPACE ts_arch_q1_1998   COMPRESS FOR ARCHIVE LOW;

ALTER TABLE sales

MOVE PARTITION sales_1995

COMPRESS FOR OLTP

PARALLEL NOLOGGING;

SQL> ALTER TABLE aud_product_big_data_clob MODIFY LOB (DESCRIPTION) (CACHE READS);

--> Moving Index :

set line 400 

col INDEX_NAME format a20 

col owner format a20

col  index_owner format a20

col segment_name format a20

col tablespace_name format a20 

col segment_type format a20

select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name|| '  tablespace HIST_INDEX2 compress advanced high ;'  from  dba_ind_partitions  where index_name in ( select segment_name from dba_segments  where  tablespace_name='HIST_INDEX'  and segment_type like 'INDEX PART%'  );

select 'alter index '||index_owner||'.'||index_name||' modify default attributes FOR PARTITION '||partition_name|| '  tablespace HIST_INDEX2 ;' from  dba_ind_partitions  where index_name in  ( 

select segment_name from dba_segments  where  tablespace_name='HIST_DATA'  and segment_type like 'INDEX PART%'  );

select 'alter index '||index_owner||'.'||index_name||' modify default attributes  tablespace HIST_INDEX2 ;'  from  dba_ind_partitions  where index_name  in ( select distinct index_name from  dba_ind_partitions  where  tablespace_name='HIST_INDEX'  ) ;

select 'alter index '||owner||'.'||index_name||' rebuild tablespace  HIST_INDEX2 compress advanced high ;'  from  dba_indexes where tablespace_name='HIST_INDEX'    ; 

select 'alter index '||owner||'.'||index_name||' modify default attributes  tablespace HIST_INDEX2 ;'  from  dba_indexes where index_name  in ( select distinct index_name from  dba_ind_partitions  where  tablespace_name='HIST_INDEX'  ) ;

--> Move Lob  to New tablespace when tables are compressed 

set line 400 

select 'ALTER TABLE '||table_owner||'.'||table_name||' MOVE partition '||Partition_name||' lob('||column_name||')'||' STORE AS (TABLESPACE  HIST_DATA2 COMPRESS high) ;' from dba_lob_partitions where TABLESPACE_NAME = 'HIST_DATA';

select 'ALTER TABLE '||owner||'.'||table_name||' MOVE LOB( '||column_name||') STORE AS (TABLESPACE  HIST_DATA2 COMPRESS high) ;' from dba_lobs where tablespace_name='HIST_DATA' and Partitioned='NO';

elect 'ALTER TABLE '||owner||'.'||table_name||' MOVE PARTITION '||Partition_name||'LOB( '||column_name||') STORE AS (TABLESPACE  HIST_DATA2 COMPRESS high) ;' from   dba_lob_partitions  where tablespace_name='HIST_DATA'  ;

select 'ALTER TABLE '|| owner||'.'||table_name||' modify default attributes  lob ('||column_name ||')  ( tablespace HIST_DATA2 ) ;'   from dba_lobs where TABLESPACE_NAME = 'HIST_DATA2' and Partitioned='YES';

select 'ALTER TABLE '|| owner||'.'||table_name||' modify default attributes for partition '||Partition_name||'lob ('||column_name ||')  ( tablespace HIST_DATA2 ) ;' from dba_lob_partitions  where tablespace_name='HIST_DATA2'  ;

alter table test modify default attributes for partition p1 lob(data) (tablespace

ALTER TABLE TRX_DATA_AAK move PARTITION M201201 tablespace ADVCLM_TBS_M201112_1_8 lob(SYS_NC00004$) store as  securefile (tablespace ADVCLM_TBS_M201112_1_8 deduplicate compress high);

ALTER TABLE blogs MODIFY LOB (blog_text)  (COMPRESS HIGH ENCRYPT DEDUPLICATE); 

Eg Commands : 

ALTER TABLE TRX_DATA_TEST move PARTITION M201005 tablespace ADVCLM_TBS_M201005_1_8 lob(SYS_NC00004$) store as  securefile (tablespace ADVCLM_TBS_M201005_1_8 compress high);

ALTER TABLE ADVCLM_QA.TRX_DATA move PARTITION M201009 tablespace ADVCLM_DATA lob(SYS_NC00004$) store as  securefile (tablespace ADVCLM_DATA deduplicate compress high);

ALTER TABLE TRX_DATA_AAK move PARTITION M201201 tablespace ADVCLM_TBS_M201112_1_8 lob(SYS_NC00004$) store as basicfile (tablespace ADVCLM_TBS_M201112_1_8  nocompress);

ALTER TABLE ETPIMPL.TRX_DATA_COMPRESS move PARTITION M201201 lob(SYS_NC00004$)  store as SECUREFILE (nocompress);

ALTER TABLE print_media MODIFY PARTITION P3 LOB(ad_sourcetext)   (RETENTION AUTO);

ALTER TABLE print_media MODIFY PARTITION P3 LOB(ad_sourcetext)   (compress high);

ALTER TABLE "MA_USER"."TEST_CLOB_COMPRESS3"  MODIFY   LOB("RTDM_RESPONSE_XML") ( compress high   );

ALTER TABLE blogs MODIFY LOB (blog_text)  (COMPRESS HIGH ENCRYPT DEDUPLICATE); 

--> Move Lob  to New tablespace when  table is not compressed 

select TABLE_NAME,LOB_NAME,PARTITION_NAME,COMPRESSION  from dba_lob_partitions where tablespace_name='HIST_DATA2';

set line 400

col table_name format a20

select TABLE_NAME,COMPRESSION from dba_lobs where tablespace_name='HIST_DATA2' and partitioned='NO'; 

set line 400 

col owner format a20

col column_name format a20

col table_name format a20 

col  tablespace_name  format a20 

select 'ALTER TABLE '||table_owner||'.'||table_name||' modify lob ('||column_name ||')  (COMPRESS high) ;'   from dba_lob_partitions where TABLESPACE_NAME = 'HIST_DATA2';

select 'ALTER TABLE '|| owner||'.'||table_name||' modify lob ('||column_name ||')  (COMPRESS high) ;'   from dba_lobs where TABLESPACE_NAME = 'HIST_DATA2' and COMPRESSION='NO'; 

--> Post  Verification to ensure all tables/Index/Lob are compressed and Moved to new  tablespace :

select segment_name , segment_type , owner from dba_segments where   tablespace_name='HIST_DATA' ;

select segment_name , segment_type , owner from dba_segments where   tablespace_name='HIST_INDEX' ;

select TABLE_NAME,LOB_NAME,PARTITION_NAME,COMPRESSION  from dba_lob_partitions where tablespace_name='HIST_DATA2';

select TABLE_NAME,COMPRESSION, COLUMN_NAME  from dba_lobs where tablespace_name='HIST_DATA2'; 

select tablespace_name , segment_type , count(*)  from dba_segments   where  tablespace_name like 'HIST_%'  group by  tablespace_name , segment_type order by 1; 

SELECT distinct TABLESPACE_NAME from dba_lobs where owner='FCC'; 

select distinct TABLESPACE_NAME from dba_lob_partitions where table_owner ='FCC'; 

SELECT distinct TABLESPACE_NAME from dba_tables where owner='FCC'; 

select distinct TABLESPACE_NAME from dba_tab_partitions where table_owner='FCC'; 

SELECT distinct TABLESPACE_NAME from dba_indexes where owner='FCC'; 

SELECT distinct TABLESPACE_NAME from dba_ind_partitions where index_owner='FCC';

-->  rename  original tablespace  HIST_INDEX and  HIST_DATA  to   HIST_INDEX3 and  HIST_DATA3

 ALTER TABLESPACE  HIST_INDEX   RENAME to  HIST_INDEX3  ;

 ALTER TABLESPACE  HIST_DATA   RENAME to  HIST_DATA3 ;

-->  rename  new tablespace  HIST_INDEX2 and  HIST_DATA2  to   HIST_INDEX and  HIST_DATA

 ALTER TABLESPACE  HIST_INDEX2   RENAME to  HIST_INDEX   ;

 ALTER TABLESPACE  HIST_DATA2   RENAME to  HIST_DATA  ;

Compression Advisory: DBMS_COMPRESSION

GET_COMPRESSION_TYPE

The GET_COMPRESSION_TYPE function displays the level of compression for a specified row in a table.

SELECT rowid,

       CASE DBMS_COMPRESSION.get_compression_type ('OWNER', 'TAB1', rowid)

         WHEN 1  THEN 'COMP_NOCOMPRESS'

         WHEN 2  THEN 'COMP_FOR_OLTP'

         WHEN 4  THEN 'COMP_FOR_QUERY_HIGH'

         WHEN 8  THEN 'COMP_FOR_QUERY_LOW'

         WHEN 16 THEN 'COMP_FOR_ARCHIVE_HIGH'

         WHEN 32 THEN 'COMP_FOR_ARCHIVE_LOW'

         WHEN 64 THEN 'COMP_BLOCK'

       END AS compression_type

FROM   tab1

WHERE  rownum <= 5;

GET_COMPRESSION_RATIO

The GET_COMPRESSION_RATIO procedure estimates the impact of different levels of compression on a specified table or partition. 

GET_COMPRESSION_RATIO procedure can now estimate the impact of different levels of compression on LOBs and indexes

comptype  Options : 

comptype => DBMS_COMPRESSION.COMP_ADVANCED

comptype => DBMS_COMPRESSION.comp_for_oltp

comptype => DBMS_COMPRESSION.COMP_QUERY_LOW;

comptype => DBMS_COMPRESSION.COMP_QUERY_HIGH;

comptype => DBMS_COMPRESSION.COMP_ARCHIVE_LOW;

comptype => DBMS_COMPRESSION.COMP_ARCHIVE_HIGH;

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  => 'USERS',

    ownname         => 'TEST',

    tabname         => 'TAB1',    -- ( Or  objname =>  ) 

    partname        => NULL,      -- ( Or  subobjname      =>  ) 

    comptype        => DBMS_COMPRESSION.comp_for_oltp,

    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

  );

  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;

/

The following example shows the effect of low-level advanced compression on an index partition, using the minimum sample size.

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  => 'USERS',

    ownname         => 'TEST',

    objname         => 'TAB1_CODE_IDX',

    subobjname      => 'TAB1_PART_2015',

    comptype        => DBMS_COMPRESSION.comp_index_advanced_low,

    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_minrows,

    objtype         => DBMS_COMPRESSION.objtype_index

  );

  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;

/

It's also possible to test all indexes for a table in a single call. 

The procedure returns a collection of records holding the data for each index.

SET SERVEROUTPUT ON

DECLARE

  l_index_cr      DBMS_COMPRESSION.compreclist;

  l_comptype_str  VARCHAR2(32767);

BEGIN

  DBMS_COMPRESSION.get_compression_ratio (

    scratchtbsname  => 'USERS',

    ownname         => 'TEST',

    tabname         => 'TAB1',

    comptype        => DBMS_COMPRESSION.comp_index_advanced_low,

    index_cr        => l_index_cr,

    comptype_str    => l_comptype_str,

    subset_numrows  => DBMS_COMPRESSION.comp_ratio_lob_maxrows

  );

  FOR i IN l_index_cr.FIRST .. l_index_cr.LAST LOOP

    DBMS_OUTPUT.put_line('----');

    DBMS_OUTPUT.put_line('ownname      : ' || l_index_cr(i).ownname);

    DBMS_OUTPUT.put_line('objname      : ' || l_index_cr(i).objname);

    DBMS_OUTPUT.put_line('blkcnt_cmp   : ' || l_index_cr(i).blkcnt_cmp);

    DBMS_OUTPUT.put_line('blkcnt_uncmp : ' || l_index_cr(i).blkcnt_uncmp);

    DBMS_OUTPUT.put_line('row_cmp      : ' || l_index_cr(i).row_cmp);

    DBMS_OUTPUT.put_line('row_uncmp    : ' || l_index_cr(i).row_uncmp);

    DBMS_OUTPUT.put_line('cmp_ratio    : ' || l_index_cr(i).cmp_ratio);

    DBMS_OUTPUT.put_line('objtype      : ' || l_index_cr(i).objtype);

  END LOOP;

END;

/

example shows the effect of compression on a CLOB in a table.

SET SERVEROUTPUT ON

DECLARE

  l_blkcnt_cmp    PLS_INTEGER;

  l_blkcnt_uncmp  PLS_INTEGER;

  l_lobcnt        PLS_INTEGER;

  l_cmp_ratio     NUMBER;

  l_comptype_str  VARCHAR2(32767);

BEGIN

  DBMS_COMPRESSION.get_compression_ratio (

    scratchtbsname  => 'USERS',

    tabowner        => 'TEST',

    tabname         => 'TAB1',

    lobname         => 'CLOB_DESCRIPTION',

    partname        => NULL,

    comptype        => DBMS_COMPRESSION.comp_lob_high,

    blkcnt_cmp      => l_blkcnt_cmp,

    blkcnt_uncmp    => l_blkcnt_uncmp,

    lobcnt          => l_lobcnt,

    cmp_ratio       => l_cmp_ratio,

    comptype_str    => l_comptype_str,

    subset_numrows  => DBMS_COMPRESSION.comp_ratio_lob_maxrows

  );

  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_lobcnt);

  DBMS_OUTPUT.put_line('Number of lobs sampled                   : ' ||  l_cmp_ratio);

  DBMS_OUTPUT.put_line('Compression type                         : ' ||  l_comptype_str);

END;

/

Compression Advisor with DBMS_ADVISOR

 

DECLARE

  task_id NUMBER;

  task_name VARCHAR2(30);

  obj_id NUMBER;

BEGIN

  task_name := 'Task01';

 

  DBMS_ADVISOR.CREATE_TASK('Compression Advisor', task_id, task_name, 'Task for table REPDB.MFG_LOT_QTY');

  DBMS_ADVISOR.CREATE_OBJECT(task_name,'TABLE','REPDB','MFG_LOT_QTY',NULL,NULL,obj_id);

  DBMS_ADVISOR.SET_TASK_PARAMETER(task_name, 'MODE', 'COMPREHENSIVE');

  DBMS_ADVISOR.EXECUTE_TASK(task_name);

END;

/

 

 SELECT message,more_info FROM DBA_ADVISOR_FINDINGS WHERE task_name='Task01';

 SELECT benefit_type FROM DBA_ADVISOR_RECOMMENDATIONS WHERE task_name='Task01';

 SELECT DBMS_ADVISOR.GET_TASK_REPORT('Task01') FROM dual;

 

 

Views : 

SELECT CASE compression_type

WHEN 1 THEN 'No Compression'

WHEN 2 THEN 'Advanced compression level'

WHEN 4 THEN 'Hybrid Columnar Compression for Query High'

WHEN 8 THEN 'Hybrid Columnar Compression for Query Low'

WHEN 16 THEN 'Hybrid Columnar Compression for Archive High'

WHEN 32 THEN 'Hybrid Columnar Compression for Archive Low'

WHEN 64 THEN 'Compressed row'

WHEN 128 THEN 'High compression level for LOB operations'

WHEN 256 THEN 'Medium compression level for LOB operations'

WHEN 512 THEN 'Low compression level for LOB operations'

WHEN 1000 THEN 'Minimum required number of LOBs in the object for which LOB compression ratio is to be estimated'

WHEN 4096 THEN 'Basic compression level'

WHEN 5000 THEN 'Maximum number of LOBs used to compute the LOB compression ratio'

WHEN 1000000 THEN 'Minimum required number of rows in the object for which HCC ratio is to be estimated'

WHEN -1 THEN 'To indicate the use of all the rows in the object to estimate HCC ratio'

WHEN 1 THEN 'Identifies the object whose compression ratio is estimated as of type table'

ELSE 'Unknown Compression Type'

END AS compression_type, n as num_rows

FROM (SELECT compression_type, Count(*) n

FROM (SELECT dbms_compression.Get_compression_type(USER, 'ARTICLE', ROWID) AS COMPRESSION_TYPE

FROM app.article)

GROUP BY compression_type

);

=> Known Issues :

1)  When  we only do  "alter table contracts_sec modify lob(orig_file) (compress HIGH)"     ,  Because just modifing the LOB with compression increases his space x2 times:

Use  move Lob instead  as below   or  use shrink lob  after performing alter  above modify  lob 

ALTER TABLE table_name  MOVE lob(lob_column_name) STORE AS SECUREFILE  ( DISABLE storage in row TABLESPACE tablespace_name   COMPRESS HIGH   CACHE )

Or 

ALTER TABLE <table_name> MODIFY LOB (<lob_column>) (SHRINK SPACE);

ALTER TABLE "MA_USER"."TEST_CLOB_COMPRESS3" MODIFY LOB (RTDM_RESPONSE_XML) (SHRINK SPACE CASCADE)

2) 

Bug 13701440 - ALTER TABLE NOT ENABLING COMPRESSION FOR SECUREFILE

FAQ:

> Will  new rows compressed on its own

> Adding column to compressed  table 

> 

==> References : 

https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/partition-table-compression.html#GUID-9F5B466B-A74F-4D0E-8A9C-EB7138DEBEFF

Note 1101900.1    Performance Issue with Update Statement in Compressed Tablespace

Note 987049.1    Performance Issue After Enabling Compression

Note 829068.1    COMPRESS FOR ALL OPERATIONS generates lot of redo

Note 1149283.1    The OLTP Compression Saves No Space As Expected Using A Row Too Big

https://docs.oracle.com/en/database/oracle/oracle-database/19/adlob/using-oracle-LOBs-storage.html#GUID-E82DC080-8AA0-46F5-B6C6-DDAB7105BC9F

https://docs.oracle.com/en/database/oracle/oracle-database/21/adlob/altering-existing-LOB-column.html#GUID-3F76D891-BAE3-463B-9109-0D7674AF8DA9

http://www.asktheway.org/official-documents/oracle/E50529_01/VLDBG/GUID-3ABCDEA5-1EE6-4D9D-98FD-DD673702A214.htm