1 Reply Latest reply: Oct 9, 2012 9:55 PM by mrmessin RSS

    Need information/advice on dataware house compression on 11g

    user569151
      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
          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