Re: Function to compress text, not encrypt

From: Tim Gorman <tim_at_sagelogix.com>
Date: Mon, 15 Mar 2004 20:51:13 -0700
Message-ID: <BC7BC7C1.1183E%tim@sagelogix.com>

Juan Cachito,

It is more space-efficient to simply store numeric values in a NUMBER datatype instead of text. Of course, it is not so apparent with short values like 11 and ³11²...

> SQL> select dump(11) from dual;
>
> DUMP(11)
> -------------------
> Typ=2 Len=2: 193,12
>
> SQL> select dump('11') from dual;
>
> DUMP('11')
> -------------------
> Typ=96 Len=2: 49,49

They both consume 2 bytes here so that represents 0% compression, but now let¹s try numbers with more significant digits:

> SQL> select dump(1111) from dual;
>
> DUMP(1111)
> ----------------------
> Typ=2 Len=3: 194,12,12
>
> SQL> select dump('1111') from dual;
>
> DUMP('1111')
> -------------------------
> Typ=96 Len=4: 49,49,49,49

Four bytes to store it as a character string, but only three bytes needed to store it as a NUMBER datatype, so that¹s 33% ³compression². If we keep this up...

> SQL> select dump (111111) from dual;
>
> DUMP(111111)
> -------------------------
> Typ=2 Len=4: 195,12,12,12
>
> SQL> select dump('111111') from dual;
>
> DUMP('111111')
> -------------------------------
> Typ=96 Len=6: 49,49,49,49,49,49

Four bytes instead of six ‹ that¹s 50% ³compression²...

> SQL> select dump(11111111) from dual;
>
> DUMP(11111111)
> ----------------------------
> Typ=2 Len=5: 196,12,12,12,12
>
> SQL> select dump('11111111') from dual;
>
> DUMP('11111111')
> -------------------------------------
> Typ=96 Len=8: 49,49,49,49,49,49,49,49

Five bytes instead of eight; that represents a 60% compression ratio...

> SQL> select dump(1111111111) from dual;
>
> DUMP(1111111111)
> -------------------------------
> Typ=2 Len=6: 197,12,12,12,12,12
>
> SQL> select dump('1111111111') from dual;
>
> DUMP('1111111111')
> --------------------------------------------
> Typ=96 Len=10: 49,49,49,49,49,49,49,49,49,49

Six bytes instead of ten; that is a 66% compression ratio. For numeric text values, you can count on (N/2)+1 where ³N² is the number of significant decimal digits. And, you can do a whole lot better when you¹re dealing with lots of zeroes...

> SQL> select dump(10000000000) from dual;
>
> DUMP(10000000000)
> ------------------
> Typ=2 Len=2: 198,2
>
> SQL> select dump('10000000000') from dual;
>
> DUMP('10000000000')
> -----------------------------------------------
> Typ=96 Len=11: 49,48,48,48,48,48,48,48,48,48,48
>

Hoo-wee! That¹s about 450% compression ratio, I think...

Hope this helps...

-Tim

on 3/12/04 1:14 PM, Juan Cachito Reyes Pacheco at jreyes_at_dazasoftware.com wrote:

> Hi, is there a function to compress test
> for example 11 you can compress to K (11th letter).
> to optimize storage in data rarely queried.
>
> Thanks
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Mar 15 2004 - 21:47:25 CST