Oracle 11gR1中的SecureFile(2)

CREATE TABLE bf_tab (
  id         NUMBER,
  clob_data  CLOB
) 
LOB(clob_data) STORE AS BASICFILE;
INSERT INTO bf_tab VALUES (1, 'My CLOB data');
COMMIT;
CREATE TABLE sf_tab (
  id         NUMBER,
  clob_data  CLOB
) 
LOB(clob_data) STORE AS SECUREFILE;
INSERT INTO sf_tab VALUES (1, 'My CLOB data');
COMMIT;

CREATE TABLE keep_duplicates_tab (
  id         NUMBER,
  clob_data  CLOB
) 
LOB(clob_data) STORE AS SECUREFILE keepdup_lob(
  KEEP_DUPLICATES
);
CREATE TABLE deduplicate_tab (
  id         NUMBER,
  clob_data  CLOB
) 
LOB(clob_data) STORE AS SECUREFILE dedup_lob (
  DEDUPLICATE
);
DECLARE
  l_clob CLOB := RPAD('X', 10000, 'X');
BEGIN
  FOR i IN 1 .. 1000 LOOP
    INSERT INTO keep_duplicates_tab VALUES (i, l_clob);
  END LOOP;
  COMMIT;
 
  FOR i IN 1 .. 1000 LOOP
    INSERT INTO deduplicate_tab VALUES (i, l_clob);
  END LOOP;
  COMMIT;
END;
/
 
EXEC DBMS_STATS.gather_table_stats(USER, 'keep_duplicates_tab');
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                          262144
KEEPDUP_LOB                      19267584
 
2 rows selected.
 
SQL>

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>