Skip to Main Content
  • Questions
  • Compressing LOB data increases table size

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rod.

Asked: September 10, 2018 - 4:09 pm UTC

Last updated: October 02, 2018 - 8:22 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

Hello,

I think this a more conceptual question:

In our core production database system, we have a pretty big table that weights 15,5TB. Of these, about 14.4TB are XML Data stored in a LOB column.

We decided to reduce the size of the database using advanced lob compression and online table redefinition.

We've run some tests in QA enviroment, which has a smaller copy of this table.

IN QA the sizes are:



Table segment 172536889344B 160,69GB
Lob index 6553600B 0,01GB
Lob segment 2284452839424B 2127,56GB


After table redefinition with medium lob compression we got the following results:



Table segment 511839305728B 476,69BG
Lob index 6553600B 0,01GB
Lob segment 474550894592B 0,21GB


As you can see, the lob segment was compressed to 20,8% of the original size, but on the other hand, the table segment increased almost 3 times its size.

We run another test applying lob compression and deduplication and advanced table compression and the results are even worse, table segment not only didn't reduce its size, it increased even more than with no table compression (501GB).

Finally, we've run aditional tests and the results are the same: LOB decreases and table increases it's size.

We were not able to find this behavior documented anywhere, that's why we are here:


Is this the expected in all scenarios?
What could be the explanation for the increase on the table size,
and finally, if table compression didn't work, should we assume that it is not possible to avoid it (the increase)?


Thank you in advance for your comments


Regards,

Rod.



Edit:

This is the original table dll:

CREATE TABLE SC.TAB
(
  C01                    CHAR(36 CHAR),
  C02                    CHAR(36 CHAR),
  C03                    CHAR(2 CHAR),
  C04                    CHAR(2 CHAR),
  C05                    TIMESTAMP(6),
  C06                    TIMESTAMP(6),
  C07           TIMESTAMP(6),
  C08              VARCHAR2(150 CHAR),
  C09               CHAR(36 CHAR),
  C10                    CHAR(36 CHAR),
  C11                    CLOB,
  C12                    NUMBER(10),
  C13                    CHAR(2 CHAR),
  C14                    VARCHAR2(2 CHAR),
  C15                    TIMESTAMP(6),
  C16                    TIMESTAMP(6),
  C17                    TIMESTAMP(6),
  C18                    CHAR(36 CHAR),
  C19                    VARCHAR2(2 CHAR),
  C20                    CHAR(36 CHAR)
)
LOB (C11) STORE AS SECUREFILE (
  TABLESPACE  SC_DATA
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  NOCACHE
  LOGGING)
NOCOMPRESS 
TABLESPACE SC_DATA
PCTUSED    0
PCTFREE    40
INITRANS   200
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
PARTITION BY HASH (C01)
(  
  PARTITION N01
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N02
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N03
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N04
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N05
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N06
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N07
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N08
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N09
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N10
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA)  

)
NOCACHE
MONITORING;



And this are the steps we used to compress the lob segment:


1. create aux table with lob compression enabled:

CREATE TABLE SC.TAB_INT
(
  C01                    CHAR(36 CHAR),
  C02                    CHAR(36 CHAR),
  C03                    CHAR(2 CHAR),
  C04                    CHAR(2 CHAR),
  C05                    TIMESTAMP(6),
  C06                    TIMESTAMP(6),
  C07           TIMESTAMP(6),
  C08              VARCHAR2(150 CHAR),
  C09               CHAR(36 CHAR),
  C10                    CHAR(36 CHAR),
  C11                    CLOB,
  C12                    NUMBER(10),
  C13                    CHAR(2 CHAR),
  C14                    VARCHAR2(2 CHAR),
  C15                    TIMESTAMP(6),
  C16                    TIMESTAMP(6),
  C17                    TIMESTAMP(6),
  C18                    CHAR(36 CHAR),
  C19                    VARCHAR2(2 CHAR),
  C20                    CHAR(36 CHAR)
)
LOB (C11) STORE AS SECUREFILE (
  TABLESPACE  SC_DATA
  ENABLE      STORAGE IN ROW
  CHUNK          8192
  COMPRESS  
  NOCACHE
  LOGGING)
NOCOMPRESS 
TABLESPACE SC_DATA
PCTUSED    0
PCTFREE    40
INITRANS   200
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
PARTITION BY HASH (C01)
(  
  PARTITION N01
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N02
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N03
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N04
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N05
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N06
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N07
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N08
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N09
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N10
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA)
)
NOCACHE
MONITORING;



2. Start online redefinition:

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(uname => 'SC',orig_table => 'TAB',int_table => 'TAB_INT',col_mapping => NULL,options_flag => 2);
END;


3. Copy table objects


SET SERVEROUTPUT ON
DECLARE
  err_cnt PLS_INTEGER;
BEGIN
 
 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (uname => 'SC',
 orig_table => 'TAB',
 int_table => 'TAB_INT',
 copy_indexes => dbms_redefinition.cons_orig_params,
 copy_triggers => TRUE,
 copy_constraints => TRUE,
 copy_privileges => TRUE,
 ignore_errors => TRUE,
 num_errors => err_cnt);
 
DBMS_OUTPUT.PUT_LINE ('Errors Count := ' || TO_CHAR (err_cnt));
 
END;


4. Sync tables
BEGIN
DBMS_REDEFINITION.sync_interim_table('SC', 'TAB', 'TAB_INT'); 
END;


5. Complete online table redefinition:

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE (uname => 'SC', orig_table => 'TAB', int_table => 'TAB_INT');
END;



These steps give the results we mentioned previously:

Before
Table segment 172536889344B 160,69GB
Lob index 6553600B 0,01GB
Lob segment 2284452839424B 2127,56GB


After
Table segment 511839305728B 476,69BG
Lob index 6553600B 0,01GB
Lob segment 474550894592B 0,21GB


Thank you for your comments







and Connor said...

It's most likely to be your ENABLE STORAGE IN ROW. If a lob is below a certain size (around 4k) then it will be stored inline with the table row, otherwise in the lob segment. If compression is bring those lobs that were once too large under that threshold, then they'll be part of the table not the lob segment, eg

SQL> CREATE TABLE t1
  2  (
  3    x int,
  4    C11                    CLOB
  5  )
  6  LOB (C11) STORE AS SECUREFILE (
  7    ENABLE      STORAGE IN ROW
  8    CHUNK          8192
  9    NOCOMPRESS
 10    NOCACHE
 11    LOGGING);

Table created.

SQL>
SQL> CREATE TABLE t2
  2  (
  3    x int,
  4    C11                    CLOB
  5  )
  6  LOB (C11) STORE AS SECUREFILE (
  7    ENABLE      STORAGE IN ROW
  8    CHUNK          8192
  9    COMPRESS
 10    NOCACHE
 11    LOGGING);

Table created.

SQL>
SQL> insert into t1
  2  select rownum, rpad(chr(65+mod(rownum,20)),6000,65+mod(rownum,20))
  3  from   dual
  4  connect by level <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select segment_name, bytes from user_segments
  2  where segment_name = 'T1' or
  3  segment_name = ( select segment_name from user_lobs
  4                   where table_name = 'T1' );

SEGMENT_NAME                        BYTES
------------------------------ ----------
T1                                 655360
SYS_LOB0000265363C00002$$       184745984

SQL>
SQL>
SQL> insert into t2
  2  select * from t1;

10000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select segment_name, bytes from user_segments
  2  where segment_name = 'T2' or
  3  segment_name = ( select segment_name from user_lobs
  4                   where table_name = 'T2' );

SEGMENT_NAME                        BYTES
------------------------------ ----------
T2                                2097152
SYS_LOB0000265366C00002$$          131072


If I repeat this with DISABLE storage in row, you'll see the table does not change much but the lob size shrinks due to the compression

SQL> CREATE TABLE t1
  2  (
  3    x int,
  4    C11                    CLOB
  5  )
  6  LOB (C11) STORE AS SECUREFILE (
  7    DISABLE      STORAGE IN ROW
  8    CHUNK          8192
  9    NOCOMPRESS
 10    NOCACHE
 11    LOGGING);

Table created.

SQL>
SQL> CREATE TABLE t2
  2  (
  3    x int,
  4    C11                    CLOB
  5  )
  6  LOB (C11) STORE AS SECUREFILE (
  7    DISABLE      STORAGE IN ROW
  8    CHUNK          8192
  9    COMPRESS
 10    NOCACHE
 11    LOGGING);

Table created.

SQL>
SQL> insert into t1
  2  select rownum, rpad(chr(65+mod(rownum,20)),6000,65+mod(rownum,20))
  3  from   dual
  4  connect by level <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select segment_name, bytes from user_segments
  2  where segment_name = 'T1' or
  3  segment_name = ( select segment_name from user_lobs
  4                   where table_name = 'T1' );

SEGMENT_NAME                        BYTES
------------------------------ ----------
T1                                 655360
SYS_LOB0000265370C00002$$       184745984

2 rows selected.

SQL>
SQL>
SQL> insert into t2
  2  select * from t1;

10000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select segment_name, bytes from user_segments
  2  where segment_name = 'T2' or
  3  segment_name = ( select segment_name from user_lobs
  4                   where table_name = 'T2' );

SEGMENT_NAME                        BYTES
------------------------------ ----------
T2                                 720896
SYS_LOB0000265373C00002$$        92471296

2 rows selected.




Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library