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
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.