Re: LOB Compression

From: Kevin Crosbie <caoimhinocrosbai_at_at_yahoo.com>
Date: Fri, 07 Jul 2006 12:09:28 +0200
Message-ID: <44ae3261$0$1548$88260bb3@news-taz.teranews.com>

DA Morgan wrote:
>
> Thanks. But where is the file you want compressed?

Well, actually I just wanted to see how the raw text I had set up in the script compressed, but a large text file is a good idea too as we won't be led astray by compression header information etc.

I downloaded the following file into my /tmp folder: wget http://www.gutenberg.org/files/18637/18637-8.txt

It should be quite compressible as it's not got much real information. ;)

If you set up a user directory as (Given proper permissions etc.): create or replace directory OS_TMP_DIR as '/tmp';

Then run:

set serveroutput on

DECLARE
   rawtext raw(32767);

   uncompressed   blob;
   compressed1    blob;
   compressed2    blob;
   uncompressed1  blob;
   uncompressed2  blob;
   v_text_file    bfile  := bfilename('OS_TMP_DIR', '18637-8.txt');
   v_length       number;

   v_dest_offset number := 1;
   v_src_offset number := 1;
BEGIN    dbms_lob.createtemporary(uncompressed, TRUE);    dbms_lob.open(uncompressed, dbms_lob.lob_readwrite);

   v_length := dbms_lob.getlength(v_text_file);    dbms_lob.fileopen (
    file_loc => v_text_file,
    open_mode => dbms_lob.file_readonly);

   dbms_lob.loadblobfromfile (

    dest_lob     => uncompressed,
    src_bfile    => v_text_file,
    amount       => v_length,
    dest_offset  => v_dest_offset,
    src_offset   => v_src_offset);

   dbms_lob.close (file_loc => v_text_file);

   dbms_output.put_line('Uncompressed Length: '

                     || dbms_lob.getlength(uncompressed));

   compressed1 := compressor.blob_compress(uncompressed);

   dbms_output.put_line('Java Compressed Length: '

                     || dbms_lob.getlength(compressed1));

   compressed2 := utl_compress.lz_compress(uncompressed);

   dbms_output.put_line('UTL Compressed Length: '

                     || dbms_lob.getlength(compressed2));

   uncompressed1 := compressor.blob_decompress(compressed1);

   dbms_output.put_line('Java Decompressed Length: '

                     || dbms_lob.getlength(uncompressed1));

   uncompressed2 := utl_compress.lz_uncompress(compressed2);

   dbms_output.put_line('UTL Uncompressed Length: '

                     || dbms_lob.getlength(uncompressed2));

   dbms_lob.freetemporary(uncompressed1);    dbms_lob.freetemporary(uncompressed2);

   BEGIN
      uncompressed1 := compressor.blob_decompress(compressed2);

      dbms_output.put_line('UTL Decompressed by Java Length: '
                        || dbms_lob.getlength(uncompressed1));

      dbms_lob.freetemporary(uncompressed2);

      EXCEPTION
        WHEN OTHERS THEN
           dbms_output.put_line('Error encountered decompressing UTL '
                             || 'Compressed BLOB with Java Decompress ('
                             || SQLCODE || '): ' || SQLERRM);

   END;    BEGIN
      uncompressed2 := utl_compress.lz_uncompress(compressed1);

      dbms_output.put_line('Java Uncompressed by UTL Length: '
                        || dbms_lob.getlength(uncompressed2));

      dbms_lob.freetemporary(uncompressed1);

      EXCEPTION
        WHEN OTHERS THEN
           dbms_output.put_line('Error encountered decompressing Java '
                             || 'Compressed BLOB with UTL Uncompress ('
                             || SQLCODE || '): ' || SQLERRM);

   END;    dbms_lob.freetemporary(compressed1);
   dbms_lob.freetemporary(compressed2);

   dbms_lob.close(uncompressed);
   dbms_lob.freetemporary(uncompressed);

END;
/

I got the following output:
Uncompressed Length: 3803470
Java Compressed Length: 1307235
UTL Compressed Length: 2266379
Java Decompressed Length: 3803470
UTL Uncompressed Length: 3803470
Error encountered decompressing UTL Compressed BLOB with Java Decompress (-29532): ORA-29532: Java call terminated by uncaught Java exception: java.util.zip.ZipException: unknown compression method Error encountered decompressing Java Compressed BLOB with UTL Uncompress (-29294): ORA-29294: A data error occurred during compression or uncompression.

That's 2.9:1 for Java Deflate and 1.68:1 for UTL_COMPRESS using the default quality of 6.

Setting the quality to 9 for UTL_COMPRESS the output was: UTL Compressed Length: 2265869
for UTL_COMPRESS, that's still 1.68:1 Received on Fri Jul 07 2006 - 05:09:28 CDT