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的時候，讓壓縮就壓縮了，而且占用空間少了一半。