This discussion is archived
1 Reply Latest reply: Oct 9, 2012 7:55 PM by mrmessin RSS

Need information/advice on dataware house compression on 11g

user569151 Newbie
Currently Being Moderated
We have an initiative to compress our ever growing datawarehouse.

OS level is AIX, db version is 11gr1, and going to go to 11gr2.

We would like to know where I can get a link regarding what is best to apply regrading compression in datawarehouse db.

I tried oracle documentation, and not sure where to look .

and plus are the compression a licensed product? any performance concerns? and any practical advices are highly appreciated here.
  • 1. Re: Need information/advice on dataware house compression on 11g
    mrmessin Oracle ACE
    Currently Being Moderated
    Table compression does not require any additional licenses the key is that the only compression is done via direct insert pretty good for a data warehouse. To get and maintain compression for regular inserts and for updates an Advanced Compression license is required.

    Performance for inserts using direct method does not appear to have any large impact to load performance, however updates and regular inserts do take a bit of a hit when using compression. Queries that have any significant IO operations appear to benefit the most from compression and the larger the number of IO operations the higher the performance impact tends to be. Keep in mind that compression does take a CPU hit for the compression and de-compression operations, while this does not appear to be really large it is a consideration if your system have CPU resource issues already.

    I have found that partitioning and compression when used together offers the best performance when accessing using the partition key and partitioning offers benefits for management as well, found range/interval partitioning offered the best management benefits for a data warehouse.

    Some references besides the documentation which is a decent place to start, also google searches on Oracle Advanced Compression returns tons of results:
    http://www.techrepublic.com/whitepapers/oracle-database-11gr2-reduces-storage-costs-and-improves-performance/1728273

    Looks at table compression and 11g Advanced compression
    http://myeverydayoracle.blogspot.com/2010/11/oracle-10g-compression-vs-11gs-advanced.html

    Basic Exaplinations and examples for compression
    http://practical-tech.blogspot.com/2012/01/oracle-11gr2-table-level-compression.html

    Partitioning
    http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-partitioning-11gr2-2010-10-189137.pdf
    http://www.oracle.com/technetwork/database/options/partitioning/ds-partitioning-11gr2-2009-09-134551.pdf

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points