This discussion is archived
3 Replies Latest reply: Oct 11, 2011 3:31 PM by Kerry.Osborne RSS

HCC And PctFree

user154136 Newbie
Currently Being Moderated
We are looking at optimizing storage space and I had a question about Hybrid Columnar Compression (HCC) and pctfree values. I believe the basic compress clause will use a pctfree of 0 unless you specify a value. I also know that compress for OLTP will use a pctfree of 10 unless you override it. My question is will HCC automatically use a pctfree of 0 or does it use some other default value. i would think for archive high, it would use a pctfree of 0, but wanted to see if anyone could confirm this.
  • 1. Re: HCC And PctFree
    Emyl Explorer
    Currently Being Moderated
    Hi,

    You can see chapter 3 of "Expert Oracle Exadata" for all details on hcc and pctfree.

    http://books.google.it/books?id=1Aqw6mrxz5AC&lpg=PA69&ots=T9pNntVGwy&dq=hcc%20pctfree&pg=PA65#v=onepage&q&f=false

    Emyl
  • 2. Re: HCC And PctFree
    603349 Explorer
    Currently Being Moderated
    HCC should use a pctfree of 0 as compression units (CUs) are only written once - rows can not be added to a CU at a later point in time so why leave free space that can never be used?


    --
    Regards,
    Greg Rahn
    http://structureddata.org
  • 3. Re: HCC And PctFree
    Kerry.Osborne Oracle ACE Director
    Currently Being Moderated
    HCC tables definitely default to 0 PCT_FREE. As Greg said, updates do not add data to compression units, rather they cause the updated row to be migrated to another (non-HCC) block. So usually there is no point in leaving extra space in the blocks. You can however create a table with a non-default PCT_FREE. The setting will be ignored for rows inserted via direct path though (i.e. HCC compressed rows). Non-direct path inserts (which will not use HCC but revert to OLTP compression) will use the non-default PCT_FREE setting though.

Legend

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