创建SecureFile LOB
基础
SecureFile LOB通过在LOB存储子句后添加SECUREFILE关键字来创建，下面的代码显示创建了两个表，第一个使用的是原来的存储格式，第二个使用的是SecureFile存储格式。
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;
|
LOB重复消除
SecureFile的DEDUPLICATE选项允许在表或分区一级上的一个LOB内消除重复数据，正如你预料的那样，这个技术与预防重写导致系统开销增大，KEEP_DUPLICATE选项明确地阻止重复消除，下面的例子对比了普通的SecureFile和重复消除SecureFile的空间使用情况。
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>
|
注意重复消除段要小很多，空间节约依赖于LOB段内的重复程度，重复模式可以使用ALTER TABLE命令进行重新设置。
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>
|
