Deduplication von Securefiles in Oracle 11g

In Oracle 11g können Large Objects dedupliziert werden, sofern das Advanced Compression Pack erworben wurde. In Oracle 11g können Large Objects dedupliziert werden, sofern das Advanced Compression Pack erworben wurde.  Dieses Skript vergleicht den Speicherplatz mit und ohne Deduplizierung.


SET ECHO OFF

CL SCR
conn hr/hr
SET SERVEROUTPUT ON
SET ECHO ON

CREATE TABLE TABLOB_UNCOMP(
 ID NUMBER,
 DOC CLOB )
 LOB(DOC) STORE AS SECUREFILE T_UNCOMP;
 
PAUSE

CREATE TABLE TABLOB_COMP(
 ID NUMBER,
 DOC CLOB )
 LOB(DOC) STORE AS SECUREFILE T_COMP
 (COMPRESS HIGH);
 
PAUSE

DECLARE
    L_CLOB CLOB := RPAD('X', 10000, 'X');
BEGIN
    FOR I IN 1..1000
    LOOP
        INSERT INTO TABLOB_UNCOMP VALUES(I,L_CLOB);
    END LOOP;
    COMMIT;

    FOR I IN 1..1000
    LOOP
        INSERT INTO TABLOB_COMP VALUES(I,L_CLOB);
    END LOOP;
    COMMIT;
END;
/
pause
EXEC DBMS_STATS.gather_table_stats(USER, 'TABLOB_UNCOMP');
EXEC DBMS_STATS.gather_table_stats(USER, 'TABLOB_COMP');
pause
COLUMN segment_name FORMAT A30
SELECT segment_name, bytes
FROM   user_segments
WHERE  segment_name IN ('T_UNCOMP', 'T_COMP')
/
SELECT segment_name, compression
FROM user_lobs
WHERE  segment_name IN ('T_UNCOMP', 'T_COMP')
/
pause

DROP TABLE TABLOB_COMP PURGE;
DROP TABLE TABLOB_UNCOMP PURGE;