Oracle SecureFile features page 2/4

ALTER TABLE deduplicate_tab MODIFY LOB(clob_data) (
KEEP_DUPLICATES
);
EXEC DBMS_STATS.gather_table_stats(USER, 'deduplicate_tab');
COLUMN segment_name FORMAT A30
SELECT segment_name, bytes
FROM user_segments
WHERE segment_name IN ('KEEPDUP_LOB', 'DEDUP_LOB');
SEGMENT_NAME BYTES
------------------------------ ----------
DEDUP_LOB 76808192
KEEPDUP_LOB 68091904
2 rows selected.
SQL>

　LOB compression

The COMPRESS option of SecureFile enables compression of LOB content at the table or partition level. Use the keywords MEDIUM and HIGH to represent the compression level. If the compression level is not specified, it is defaulted to MEDIUM. Compressing LOB content will increase system overhead. Therefore, using high-level compression may have adverse effects on system performance. The compression function of SecureFile LOB does not affect table compression, and vice versa. The following example compares the space utilization of using ordinary SecureFile LOBs and SecureFile LOBs with compression enabled.

CREATE TABLE nocompress_tab ( id NUMBER, clob_data CLOB ) LOB(clob_data) STORE AS SECUREFILE nocompress_lob( NOCOMPRESS ); CREATE TABLE compress_tab ( id NUMBER, clob_data CLOB ) LOB(clob_data) STORE AS SECUREFILE compress_lob ( COMPRESS HIGH ); DECLARE l_clob CLOB := RPAD('X', 10000, 'X'); BEGIN FOR i IN 1 .. 1000 LOOP INSERT INTO nocompress_tab VALUES (i, l_clob); END LOOP; COMMIT; FOR i IN 1 .. 1000 LOOP INSERT INTO compress_tab VALUES (i, l_clob); END LOOP;

ALTER TABLE deduplicate_tab MODIFY LOB(clob_data) (
KEEP_DUPLICATES
);
EXEC DBMS_STATS.gather_table_stats(USER, 'deduplicate_tab');
COLUMN segment_name FORMAT A30
SELECT segment_name, bytes
FROM user_segments
WHERE segment_name IN ('KEEPDUP_LOB', 'DEDUP_LOB');
SEGMENT_NAME BYTES
------------------------------ ----------
DEDUP_LOB 76808192
KEEPDUP_LOB 68091904
2 rows selected.
SQL>

　LOB compression

The COMPRESS option of SecureFile enables compression of LOB content at the table or partition level. Use the keywords MEDIUM and HIGH to represent the compression level. If the compression level is not specified, it is defaulted to MEDIUM. Compressing LOB content will increase system overhead. Therefore, using high-level compression may have adverse effects on system performance. The compression function of SecureFile LOB does not affect table compression, and vice versa. The following example compares the space utilization of using ordinary SecureFile LOBs and SecureFile LOBs with compression enabled.

CREATE TABLE nocompress_tab ( id NUMBER, clob_data CLOB ) LOB(clob_data) STORE AS SECUREFILE nocompress_lob( NOCOMPRESS ); CREATE TABLE compress_tab ( id NUMBER, clob_data CLOB ) LOB(clob_data) STORE AS SECUREFILE compress_lob ( COMPRESS HIGH ); DECLARE l_clob CLOB := RPAD('X', 10000, 'X'); BEGIN FOR i IN 1 .. 1000 LOOP INSERT INTO nocompress_tab VALUES (i, l_clob); END LOOP; COMMIT; FOR i IN 1 .. 1000 LOOP INSERT INTO compress_tab VALUES (i, l_clob); END LOOP;