Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Table Compression, when to use it

Re: Table Compression, when to use it

From: Mladen Gogala <mgogala.SPAM_ME.NOT_at_verizon.net>
Date: Fri, 27 Apr 2007 05:31:21 GMT
Message-ID: <pan.2007.04.27.05.31.22@verizon.net>


On Wed, 25 Apr 2007 06:31:01 -0700, Ben wrote:

> 9.2.0.5 Ent Ed AIX5L
>
> So I read an article on table compression this morning and it sounds
> like good stuff.

The main use of compression is not saving space but cutting down on the I/O. Basically, you will have many fewer blocks in the compressed table then in an uncompressed one, which will save you some I/O at the expense of some CPU. So, when is there a dire need to save some I/O?

  1. When you're having large range scans or full scans that you need done quickly.
  2. When you want to make the table so small that full scan will cause no physical reads. That is usually accomplished by COMPRESS and CACHE attributes. In my opinion IOT are much better for small lookup tables but sometimes compressed and cached table is just as good solution.
  3. When you have very write-intensive application and relatively slow peripherals. I frequently see compressed tables used on Linux boxes which have notoriously low I/O capacity. I work for a company that is loading tons of data (~ 3,000,000 records a day) into several tables for later processing. I tuned I/O well (6 4GB redo logs, 8MB log buffer, 2 DB writer processes, asynchronous I/O, 20 freelists, 25% of free space, 32 inittrans, reverse index for primary key and the analog boxes feeding the database were still too fast. I also went after the Linux kernel and fixed network parameters, Ext3 mount options and the Ethernet drivers. My compressing few critical tables made a difference and caused the boxes to report backlog once a week instead of once a day. It has cut down the number of I/O requests for approximately 12% which was enough. Once we overgrow that, the only thing to do will be to buy a box with more I/O bandwidth.
  4. When your application has been designed by little bearded people with funny hats, living in a swamp and you desperately need to cut down on the number of blocks read, you compress everything in sight when all other options are exhausted. I've seen many applications that I would punish by slow and painful death, if it was legal. Table compression can sometimes help you, but not as much as imagining the responsible developer in a pool full of piranhas. Joy starts from within.

If your table is compressed you cannot add or drop columns. You have to rebuild the entire table. If your schema is not stable and is prone to frequent changes, don't do it.

-- 
http://www.mladen-gogala.com
Received on Fri Apr 27 2007 - 00:31:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US