Re: VARCHAR2 compress

You could create decompress() deterministic function and a functional index on it for queries similar to these:

select c1 from table where decompress(c1) (= '...' | like '...')

but index maintenance will surely make a hit on DML performance since you will need to compress data to insert or update, and Oracle will have to call your decompress() function to index your new row resulting in two unnecessary codec calls. Also, uncompressed image of every row will be stored in the index, effectively defeating your goal of minimizing space requirements - this approach actually adds the need for compressed image storage along with uncompressed image. But if you will not use functional index, your queries will run very slow, because each of them will be performing full table scan decompressing each row and comparing it to those requested in WHERE clause.

Conclusion? Don't try to save space using compression, or you'll face serious performance problems.

Corrections and additions welcome.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Colin McGuire" <colinandkaren_at_lycos.co.uk> wrote in message
news:ab6cea37.0206232357.70260ec3_at_posting.google.com...


> Thanks for the comment.

>

> In fact I'm doing something very similar (I'm populating the column

> with a compressed string created using java.util.zip) but wanted to

> canvas the field for Oracle compressed datatypes in-case I was

> performing an additional and unnecessary action.

> Regards

> Colin

>

> "Richard Armstrong" <richard.armstrong_at_juno.demon.co.uk> wrote in message

news:<1024695387.23505.0.nnrp-13.3e3123e3_at_news.demon.co.uk>...


> > Could you compress the strings first using a tool like PKZIP then store them

> > in a binary field like a BLOB?

> >

> > Richard

> >

> > "Colin McGuire" <colinandkaren_at_lycos.co.uk> wrote in message

> > news:ab6cea37.0206200018.727af1ee_at_posting.google.com...

> > > Hello - I have squillions of long strings that I would like to store

> > > in an Oracle 8 database. Because of the size of the strings I would

> > > like them to take the minimum amount of space in my database/on the

> > > server.

> > >

> > > The contrived SQL I would like to enter would be something like

> > >

> > > CREATE TABLE tblTest(id NUMBER PRIMARY KEY,

> > >                      storedate DATE,

> > >                      staffid   VARCHAR2(5),

> > >                      otherdata COMPRESSED VARCHAR2(3000)

> > >                     );

> > >

> > > So how is this done or does it need to be done (ie does Oracle

> > > automatically compress 'otherdata' for me) ?

> > >

> > > Thank you

> > > Colin