[Oracle 9i] Compression Blob Intéret ?

**MAGNUM_HEAD** · 28/02/2007, 11h36

Oracle 9i
Os Win2k

Bonjour,

J'ai utilisé le package (en fin de message) afin de réaliser des compressions de blob pour alléger une table. Après plusieurs test je me suis apperçu qu'il semble que cela ne change rien en terme d'espace...

En effet, j'ai modifié le test proposé en rajoutant GetChunksize(en gras dans le code) à chaque Blob traîté et la valeur est toujours la même.

Il y a t'il un intéret à utiliser la compression ?

Merci pour votre aide.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
SQL> set serveroutput on
SQL> DECLARE
  2    src varchar2(32767);
  3    srcRaw raw(32767);
  4    zip raw(32767);
  5    unzip varchar2(32767);
  6    tmp varchar2(250);
  7    tmpRaw raw(250);
  8    srcBlob BLOB;
  9    zipBlob BLOB;
 10    unzipBlob BLOB;
 11    read_amount binary_integer;
 12  BEGIN
 13    src := 'Many sources of information contain redundant data or data that adds '||
 14           'little to the stored information. This results in tremendous amounts '||
 15           'of data being transferred between client and server applications or '||
 16           'computers in general. The obvious solution to the problems of data '||
 17           'storage and information transfer is to install additional storage '||
 18           'devices and expand existing communication facilities. To do so, however, '||
 19           'requires an increase in an organization''s operating costs. One method '||
 20           'to alleviate a portion of data storage and information transfer is '||
 21           'through the representation of data by more efficient code. This article '||
 22           'shows how to compress and decompress data, efficiently and conveniently, '||
 23           'from within your ORACLE applications using the GZIP package.';
 24
 25
 26    DBMS_OUTPUT.put_line('2. BLOB TEST');
 27    DBMS_OUTPUT.put_line('============');
 28
 29    -- Prepare temporary Blobs
 30    DBMS_LOB.CreateTemporary(srcBlob, TRUE);
 31    DBMS_LOB.CreateTemporary(zipBlob, TRUE);
 32    DBMS_LOB.CreateTemporary(unzipBlob, TRUE);
 33
 34    DBMS_OUTPUT.put_line('2.1 SOURCE BLOB:');
 35    srcRaw := UTL_RAW.cast_to_raw(src); -- Don't forget explicit convertion!
 36    DBMS_LOB.write(srcBlob, UTL_RAW.length(srcRaw), 1, srcRaw); -- Fill source blob
 37
 38    -- Print results
 39    read_amount := 60;
 40    DBMS_LOB.read(srcBlob, read_amount, 1, tmpRaw);
 41    DBMS_OUTPUT.put_line('  Cut = '||UTL_RAW.cast_to_varchar2(tmpRaw)||' ...');
 42    DBMS_OUTPUT.put_line('  Length = '||to_char(DBMS_LOB.GetLength(srcBlob)));
 43    DBMS_OUTPUT.put_line('  Size = '||to_char(DBMS_LOB.GetChunksize(srcBlob)));
 44
 45    DBMS_OUTPUT.put_line('2.2 COMPRESSED BLOB:');
 46    GZIP.packBlob(srcBlob, zipBlob); -- Compress srcBlob into zipBlob
 47    -- Print results
 48    read_amount := 60;
 49    DBMS_LOB.read(zipBlob, read_amount, 1, tmpRaw);
 50    tmp := '';
 51    for i in 1 .. 15 loop
 52      tmp := tmp||'0x'||UTL_RAW.substr(tmpRaw, i, 1)||' ';
 53    end loop;
 54    DBMS_OUTPUT.put_line('  Cut = '||tmp||' ...');
 55    DBMS_OUTPUT.put_line('  Length = '||to_char(DBMS_LOB.GetLength(zipBlob)));
 56    DBMS_OUTPUT.put_line('  Size = '||to_char(DBMS_LOB.GetChunksize(zipBlob)));
 57
 58    DBMS_OUTPUT.put_line('2.3 DECOMPRESSED BLOB:');
 59    GZIP.unpackBlob(zipBlob, unzipBlob); -- Decompress zipBlob into unzipBlob
 60    -- Print results
 61    read_amount := 60;
 62    DBMS_LOB.read(unzipBlob, read_amount, 1, tmpRaw);
 63    DBMS_OUTPUT.put_line('  Cut = '||UTL_RAW.cast_to_varchar2(tmpRaw)||' ...');
 64    DBMS_OUTPUT.put_line('  Length = '||to_char(DBMS_LOB.GetLength(unzipBlob)));
 65    DBMS_OUTPUT.put_line('  Size = '||to_char(DBMS_LOB.GetChunksize(unzipBlob)));
 66
 67    -- Release temporary blobs
 68    DBMS_LOB.FreeTemporary(srcBlob);
 69    DBMS_LOB.FreeTemporary(zipBlob);
 70    DBMS_LOB.FreeTemporary(unzipBlob);
 71  END;
 72  /
2. BLOB TEST
============
2.1 SOURCE BLOB:
Cut = Many sources of information contain redundant data or data t ...
Length = 754
Size = 6084
2.2 COMPRESSED BLOB:
Cut = 0x1F 0x8B 0x08 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x95 0x92 0xCB 0x8E 0x14
...
Length = 408
Size = 6084
2.3 DECOMPRESSED BLOB:
Cut = Many sources of information contain redundant data or data t ...
Length = 754
Size = 6084

ProcÚdure PL/SQL terminÚe avec succÞs.

Compress / decompress Oracle data package

Name: GZIP - Compress / decompress oracle data package

Oracle version: 8.1.5 and higher

Description: Implements pl/sql pack and unpack methods using JDK java.util.zip package for both RAW and BLOB Oracle types

Methods:

FUNCTION pack(buf IN raw) RETURN raw;
FUNCTION unpack(buf IN raw) RETURN raw;
PROCEDURE packBlob(src IN blob, dst IN OUT blob);
PROCEDURE unpackBlob(src IN blob, dst IN OUT blob);

Installation steps:

1. Create java class to implement gzip methods:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
CREATE OR REPLACE JAVA SOURCE NAMED "GZIPImpl"
      AS
      import java.io.*;
      import java.util.zip.*;
      import java.sql.*;
      import oracle.sql.*;
 
      public class GZIPImpl
      {
        public static byte[] packRaw(byte[] b) {
          ByteArrayOutputStream outBuffer = new ByteArrayOutputStream();
          try {
            GZIPOutputStream gzip = new GZIPOutputStream(outBuffer);
            gzip.write(b);
            gzip.close();
          }
          catch (IOException e) {
            System.err.println(e);     
          }
          return outBuffer.toByteArray();
        }
 
        public static byte[] unpackRaw(byte[] b) {
          ByteArrayOutputStream outBuffer = new ByteArrayOutputStream();
          ByteArrayInputStream inBuffer = new ByteArrayInputStream(b);
          try {
            GZIPInputStream gzip = new GZIPInputStream(inBuffer);
            byte[] tmpBuffer = new byte[256];
            int n;
            while ((n = gzip.read(tmpBuffer)) >= 0)
              outBuffer.write(tmpBuffer, 0, n);
          }
          catch (IOException e) {
            System.err.println(e);     
          }
          return outBuffer.toByteArray();
        }
 
        public static void packBlob(oracle.sql.BLOB srcBlob, oracle.sql.BLOB dstBlob[]) {
          try {
            OutputStream outBuffer = dstBlob[0].getBinaryOutputStream();
            InputStream inBuffer = srcBlob.getBinaryStream();
            GZIPOutputStream gzip = new GZIPOutputStream(outBuffer);
            byte[] tmpBuffer = new byte[256];
            int n;
            while ((n = inBuffer.read(tmpBuffer)) >= 0)
              gzip.write(tmpBuffer, 0, n);
            gzip.close();
          }
          catch (SQLException e) {
            System.err.println(e);     
          }
          catch (IOException e) {
            System.err.println(e);     
          }
        }
 
        public static void unpackBlob(oracle.sql.BLOB srcBlob, oracle.sql.BLOB dstBlob[]) {
          try {
            OutputStream outBuffer = dstBlob[0].getBinaryOutputStream();
            InputStream inBuffer = srcBlob.getBinaryStream();
            GZIPInputStream gzip = new GZIPInputStream(inBuffer);
            byte[] tmpBuffer = new byte[256];
            int n;
            while ((n = gzip.read(tmpBuffer)) >= 0)
              outBuffer.write(tmpBuffer, 0, n);
            outBuffer.close();
          }
          catch (SQLException e) {
            System.err.println(e);     
          }
          catch (IOException e) {
            System.err.println(e);     
          }
        }
 
      };
      /

2. Compile java class and check for errors:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
alter java source "GZIPImpl" compile
      /
      show errors

3. Create a wrapper PL/SQL package:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE PACKAGE GZIP AS
        FUNCTION pack(buf IN raw) RETURN raw;
        FUNCTION unpack(buf IN raw) RETURN raw;
        PROCEDURE packBlob(src IN blob, dst IN OUT blob);
        PROCEDURE unpackBlob(src IN blob, dst IN OUT blob);
      END;
      /
 
      CREATE PACKAGE BODY GZIP AS
        FUNCTION pack(buf IN raw) RETURN raw
        AS LANGUAGE JAVA
        NAME 'GZIPImpl.packRaw(byte[]) return byte[]';
 
        FUNCTION unpack(buf IN raw) RETURN raw
        AS LANGUAGE JAVA
        NAME 'GZIPImpl.unpackRaw(byte[]) return byte[]';
 
        PROCEDURE packBlob(src IN blob, dst IN OUT blob)
        AS LANGUAGE JAVA
        NAME 'GZIPImpl.packBlob(oracle.sql.BLOB, oracle.sql.BLOB[])';
 
        PROCEDURE unpackBlob(src IN blob, dst IN OUT blob)
        AS LANGUAGE JAVA
        NAME 'GZIPImpl.unpackBlob(oracle.sql.BLOB, oracle.sql.BLOB[])';
      END;
      /

4. Test GZIP package:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
DECLARE
        src varchar2(32767);
        srcRaw raw(32767);
        zip raw(32767);
        unzip varchar2(32767);
        tmp varchar2(250);
        tmpRaw raw(250);
        srcBlob BLOB;
        zipBlob BLOB;
        unzipBlob BLOB;
        read_amount binary_integer;
      BEGIN
        src := 'Many sources of information contain redundant data or data that adds '||
               'little to the stored information. This results in tremendous amounts '||
               'of data being transferred between client and server applications or '||
               'computers in general. The obvious solution to the problems of data '||
               'storage and information transfer is to install additional storage '||
               'devices and expand existing communication facilities. To do so, however, '||
               'requires an increase in an organization''s operating costs. One method '||
               'to alleviate a portion of data storage and information transfer is '||
               'through the representation of data by more efficient code. This article '||
               'shows how to compress and decompress data, efficiently and conveniently, '||
               'from within your ORACLE applications using the GZIP package.';
 
        DBMS_OUTPUT.put_line('1. RAW TEST');
        DBMS_OUTPUT.put_line('===========');
 
        DBMS_OUTPUT.put_line('1.1 SOURCE TEXT:');
        DBMS_OUTPUT.put_line('  Cut = '||substr(src, 1, 60)||' ...');
        DBMS_OUTPUT.put_line('  Length = '||length(src));
 
        DBMS_OUTPUT.put_line('1.2 COMPRESSED RAW:');
        srcRaw := UTL_RAW.cast_to_raw(src); -- Don't forget explicit convertion!
        zip := GZIP.pack(srcRaw);
        tmp := '';
        for i in 1 .. 15 loop
          tmp := tmp||'0x'||UTL_RAW.substr(zip, i, 1)||' ';
        end loop;
        DBMS_OUTPUT.put_line('  Cut = '||tmp||' ...');
        DBMS_OUTPUT.put_line('  Length = '||UTL_RAW.length(zip));
 
        DBMS_OUTPUT.put_line('1.3 DECOMPRESSED TEXT:');
        unzip := UTL_RAW.cast_to_varchar2(GZIP.unpack(zip)); -- Decompress and convert to varchar2
        DBMS_OUTPUT.put_line('  Cut = '||substr(unzip, 1, 60)||' ...');
        DBMS_OUTPUT.put_line('  Length = '||length(unzip));
 
        DBMS_OUTPUT.put_line('2. BLOB TEST');
        DBMS_OUTPUT.put_line('============');
 
        -- Prepare temporary Blobs
        DBMS_LOB.CreateTemporary(srcBlob, TRUE);
        DBMS_LOB.CreateTemporary(zipBlob, TRUE);
        DBMS_LOB.CreateTemporary(unzipBlob, TRUE);
 
        DBMS_OUTPUT.put_line('2.1 SOURCE BLOB:');
        srcRaw := UTL_RAW.cast_to_raw(src); -- Don't forget explicit convertion!
        DBMS_LOB.write(srcBlob, UTL_RAW.length(srcRaw), 1, srcRaw); -- Fill source blob
        -- Print results
        read_amount := 60;
        DBMS_LOB.read(srcBlob, read_amount, 1, tmpRaw); 
        DBMS_OUTPUT.put_line('  Cut = '||UTL_RAW.cast_to_varchar2(tmpRaw)||' ...');
        DBMS_OUTPUT.put_line('  Length = '||to_char(DBMS_LOB.GetLength(srcBlob))); 
 
        DBMS_OUTPUT.put_line('2.2 COMPRESSED BLOB:');
        GZIP.packBlob(srcBlob, zipBlob); -- Compress srcBlob into zipBlob
        -- Print results
        read_amount := 60;
        DBMS_LOB.read(zipBlob, read_amount, 1, tmpRaw); 
        tmp := '';
        for i in 1 .. 15 loop
          tmp := tmp||'0x'||UTL_RAW.substr(tmpRaw, i, 1)||' ';
        end loop;
        DBMS_OUTPUT.put_line('  Cut = '||tmp||' ...');
        DBMS_OUTPUT.put_line('  Length = '||to_char(DBMS_LOB.GetLength(zipBlob))); 
 
        DBMS_OUTPUT.put_line('2.3 DECOMPRESSED BLOB:');
        GZIP.unpackBlob(zipBlob, unzipBlob); -- Decompress zipBlob into unzipBlob
        -- Print results
        read_amount := 60;
        DBMS_LOB.read(unzipBlob, read_amount, 1, tmpRaw); 
        DBMS_OUTPUT.put_line('  Cut = '||UTL_RAW.cast_to_varchar2(tmpRaw)||' ...');
        DBMS_OUTPUT.put_line('  Length = '||to_char(DBMS_LOB.GetLength(unzipBlob))); 
 
        -- Release temporary blobs
        DBMS_LOB.FreeTemporary(srcBlob);
        DBMS_LOB.FreeTemporary(zipBlob);
        DBMS_LOB.FreeTemporary(unzipBlob);
      END;
      /

Code above should produce next results:

1. RAW TEST
===========
1.1 SOURCE TEXT:
Cut = Many sources of information contain redundant data or data t ...
Length = 754
1.2 COMPRESSED RAW:
Cut = 0x1F 0x8B 0x08 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x95 0x92 0xCB 0x8E 0x14 ...
Length = 408
1.3 DECOMPRESSED TEXT:
Cut = Many sources of information contain redundant data or data t ...
Length = 754
2. BLOB TEST
============
2.1 SOURCE BLOB:
Cut = Many sources of information contain redundant data or data t ...
Length = 754
2.2 COMPRESSED BLOB:
Cut = 0x1F 0x8B 0x08 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x95 0x92 0xCB 0x8E 0x14 ...
Length = 408
2.3 DECOMPRESSED BLOB:
Cut = Many sources of information contain redundant data or data t ...
Length = 754

Examples

1. Compress/decompress on fly stored data using DML (less when 4000 bytes in size):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
       insert into my_table (compressed_text) values (GZIP.pack(UTL_RAW.cast_to_raw('1234567890')));
       select UTL_RAW.cast_to_varchar2(GZIP.unpack(compressed_text)) from my_table;

2. Compress/decompress BLOB stored data (size <= 4Gb):

DECLARE
SrcBlobLocator BLOB;
DstBlobLocator BLOB;
BEGIN
select blob_data into SrcBlobLocator from src_table where id=1;
select blob_data into DstBlobLocator from dst_table where id=1;
GZIP.packBlob(SrcBlobLocator, DstBlobLocator);
commit;
END;