Oracle 非结构化数据的存储：Secure File

SecureFile

1.       在Oracle中存非结构化数据主要有两种方式：以LOB字段存在DB中，在DB中保存指向外部OS文件的引用。

2.       使用文件来存储非结构化数据较为常见，它有如下好处：

a)       OS files can be cached by theOS and journaled file systems that expedite recovery after crashes. 由于操作系统以及日志文件系统会对文件进行缓存，系统崩溃后进行恢复时速度会快一些。

b)       支持压缩，占用空间更少。

c)       可使用工具来识别文件中的重复模式以提高存储效率

3.       基于文件存储非结构化数据的坏处数据不在DB中，不具备DB数据的一些特性：

a)       木有备份

b)       不支持细粒度的安全控制

c)       不参与事务，读一致什么的在DB中极常用的概念，在这里不适用

4.       SecureFiles结合了文件存储和LOB存储的长处，我去。。这么牛。同时以BasicFiles形式继续支持老的BLOB

5.       存大量PDF，在11g以前的话：

createtable contracts_basic

(

        contract_id     number(12),

        contract_name   varchar2(80),

        file_size       number,

        orig_file       blob

)

tablespaceusers

lob (orig_file) STORE AS

(

        tablespaceusers

        enablestorageinrow

        chunk4096

        pctversion20

        nocache

        nologging

);

\

6.       上面的参数指明：对LOB进行操作的时候不应当缓存或者写日志，将LOB以IN LINE的形式存在表的各行里、CHUNK的大小为4K。

7.       在11g中，默认情况下还是使用BasicFiles来存LOB

创建SecureFile

8.       要用SecureFile的话：

createtable contracts_sec

(

        contract_id     number(12),

        contract_name   varchar2(80),

        file_size       number,

        orig_file       blob

)

tablespaceusers

lob (orig_file)                              

storeassecurefile

(

        tablespaceusers

        enablestorageinrow

        chunk4096

        pctversion20

        nocache

        nologging

)

/

9.       创建好之后，在pl/sql Developer里右击表查看表的SQL语句，不会显示 SECUREFILE相关的语句。

10.   有两个前提条件，一般默认都已经满足：

a)       db_securefile的值应当是permitted

b)       放SecureFile的表空间应当使用Automatic Segment Space Management (ASSM)。在11g中表空间默认就是使用这种方式来创建的。

11.   之后，SecureFile的操作与BasicFile——原来的操作方式，相同。

12.   在SYS里创建一个指向d:\newbooks的目录对象，并允许别的用户访问它：

CREATE DIRECTORY SECFILE AS'd:\newbooks';

grant read on directory secfile topublic

13.   把一个PDF文件向一个表中插入100次：

declare

    l_size     number; 

    l_file_ptr bfile;  

    l_blob     blob;   

begin

    l_file_ptr := bfilename('SECFILE', 'contract.pdf');

    dbms_lob.fileopen(l_file_ptr);

    l_size := dbms_lob.getlength(l_file_ptr);

    for ctr in1 .. 100loop

        insertinto contracts_sec

        (       

            contract_id,

            contract_name,

            file_size,  

            orig_file   

        )       

        values  

        (       

            ctr,        

            'Contract '||ctr,

            null,       

            empty_blob()

        )       

        returning orig_file into l_blob; 

        dbms_lob.loadfromfile(l_blob,l_file_ptr, l_size);

    endloop;

    commit;

    dbms_lob.close(l_file_ptr);

end;

/

去重Deduplication

14.   假设一个表BLOB列，一共5行，其中3行的BLOB值一样。如果能像某此文件系统那样，只在一个BLOB中存实际的值，在另两个BLOB中只存一个引用，那将能大大节省空间。10g以前，不行。。

15.   可以就这么改一下就好了：

altertable contracts_sec

   modifylob(orig_file)

    (deduplicate)

    /

16.   这样设置之后，DB会计算每一行的LOB的HASH值，并将他们相互比较，如果HASH值相匹配，则在那一列存放HASH值，而不是实际的LOB值。插入新行的时候也是，计算其LOB值的HASH值，如果已经有了，则在LOB列只存一个哈希值，否则就存LOB值本身。这样能大大节省空间：

17.   查看LOB占用的空间：

declare 

   l_segment_name          varchar2(30);

   l_segment_size_blocks   number;

   l_segment_size_bytes    number;

   l_used_blocks           number; 

   l_used_bytes            number; 

   l_expired_blocks        number; 

   l_expired_bytes         number; 

   l_unexpired_blocks      number; 

   l_unexpired_bytes       number; 

begin

   select segment_name

   into l_segment_name

   from dba_lobs

   where table_name = 'CONTRACTS_SEC';

        dbms_output.put_line('Segment Name=' || l_segment_name);

   dbms_space.space_usage(

        segment_owner           => 'SCOTT', 

        segment_name            => l_segment_name,

        segment_type            => 'LOB',

        partition_name          => NULL,

        segment_size_blocks     => l_segment_size_blocks,

        segment_size_bytes      => l_segment_size_bytes,

        used_blocks             => l_used_blocks,

        used_bytes              => l_used_bytes,

        expired_blocks          => l_expired_blocks,

        expired_bytes           => l_expired_bytes,

        unexpired_blocks        => l_unexpired_blocks,

        unexpired_bytes         => l_unexpired_bytes

   );  

   dbms_output.put_line('segment_size_blocks      => '||  l_segment_size_blocks);

   dbms_output.put_line('segment_size_bytes       => '||  l_segment_size_bytes);

   dbms_output.put_line('used_blocks              => '||  l_used_blocks);

   dbms_output.put_line('used_bytes               => '||  l_used_bytes);

   dbms_output.put_line('expired_blocks           => '||  l_expired_blocks);

   dbms_output.put_line('expired_bytes            => '||  l_expired_bytes);

   dbms_output.put_line('unexpired_blocks         => '||  l_unexpired_blocks);

   dbms_output.put_line('unexpired_bytes          => '||  l_unexpired_bytes);

end;

/

18.   如果不压缩的话，插入那100个文件，上面的脚本的输出为：

SegmentName=SYS_LOB0000094304C00004$$

segment_size_blocks       => 1936

segment_size_bytes        => 15859712

used_blocks               => 1600

used_bytes                => 13107200

expired_blocks            => 290

expired_bytes             => 2375680

unexpired_blocks          => 0

unexpired_bytes          => 0

19.   压缩之后

SegmentName=SYS_LOB0000094304C00004$$

segment_size_blocks       => 3344

segment_size_bytes        => 27394048

used_blocks               => 16

used_bytes                => 131072

expired_blocks            => 87

expired_bytes             => 712704

unexpired_blocks          => 3184

unexpired_bytes           => 26083328

PL/SQL procedure successfully completed

20.   整整差100倍。因为插入100条重复的，记录一次，就够了，另外99条，只要记个哈希值就行了。

21.   如果想取消去重的话：

altertable contracts_sec

 modifylob(orig_file)

 (keep_duplicates)

  /

22.   这时，占用的空间又变回原来的值了：

SegmentName=SYS_LOB0000094304C00004$$

segment_size_blocks       => 3472

segment_size_bytes        => 28442624

used_blocks               => 1600

used_bytes                => 13107200

expired_blocks            => 6

expired_bytes             => 49152

unexpired_blocks          => 1808

unexpired_bytes          => 14811136

压缩Compression

23.   语法：

altertable contracts_sec

 modifylob(orig_file)

 (compresshigh)

24.     为什么我的压缩之后还是原来的一样大呢？

SegmentName=SYS_LOB0000094304C00004$$

segment_size_blocks       => 3728

segment_size_bytes        => 30539776

used_blocks               => 1600

used_bytes                => 13107200

expired_blocks            => 468

expired_bytes             => 3833856

unexpired_blocks          => 1600

unexpired_bytes           => 13107200

PL/SQL procedure successfully completed

25.   压缩在LOB内部进行，去重，在LOB之间进行。可去重并且压缩。

26.   压缩会占用CPU，所以，如果压不压缩得看划不划得来。如果LOB里要存已经压缩好了的JPEG，那就没什么必要压缩，如果是CLOB里要存XML文件，则压缩就值得考 虑。

27.   DB会自动识别是否值得压缩，并且只有认为值得压缩的时候才会压缩。所以，我这里应该是DB认为不值当。

28.   试试导些XMl进去。

29.   重建前面的表，这次导入大小为482KB的TXT文件

30.   TXT用CLOB存，比用BLOB存快多了。 有EMPTY_BLOB（） 也有EMPTY_CLOB（）

31.   100个482KB的TXT文件，压缩前：

SegmentName=SYS_LOB0000094312C00004$$

segment_size_blocks       => 6928

segment_size_bytes        => 56754176

used_blocks               => 6200

used_bytes                => 50790400

expired_blocks            => 639

expired_bytes             => 5234688

unexpired_blocks          => 0

unexpired_bytes           => 0

PL/SQL procedure successfully completed

32.   100个TXT的大小应当为49356800BYTE，存到DB里之后为50790400BYTE，增大的比例为（50790400-49356800）/49356800=0.029045643153527。还好。

33.   压缩之后再看：

34.   用时6.412S，

35.   果然变小了：

SegmentName=SYS_LOB0000094312C00004$$

segment_size_blocks       => 9488

segment_size_bytes        => 77725696

used_blocks               => 3100

used_bytes                => 25395200

expired_blocks            => 75

expired_bytes             => 614400

unexpired_blocks          => 6200

unexpired_bytes           => 50790400

PL/SQL procedure successfully completed

36.   （50790400-25395200）/50790400 = 0.5. 整整少了一倍。

37.   也就是说，我存进去PDF的时候，它觉得不划算，虽然让压缩了，也不压缩，存进去TXT的时候，让压缩就压缩了，而且占用空间少了一半。