Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Maria Colgan

Thanks for the question, Sagar.

Asked: February 10, 2017 - 4:26 am UTC

Last updated: February 11, 2017 - 2:22 am UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

Hi Tom,

We are looking for opportunities to free-up space in our data warehouse. The database is running on Exadata box and the version is Oracle Database 11g Enterprise Edition Release 11.2.0.4.0. Since it is a data warehouse we usually insert data in the tables and updates are close to 0. The current PCT set on the tables is either 10% or 20%. Please note that all tables are compressed using EHCC (Query High Mode). We thought that reducing the PCT from 10% to 0 or 1% would free up space to some extent and we carried out a proof of concept to test the same. Here are the results that we got.


Size of the uncompressed table with PCT10 - 952 MB
Size of the uncompressed table with PCT0 - 864 MB
Size of the compressed table with PCT10 - 144 MB
Size of the compressed table with PCT0 - 144 MB

As you can see reducing the PCT from 10 to 0 has given clear space savings in case of uncompressed tables. However, in case of compressed tables there is no benefit whatsoever. I tried searching on the internet for any articles explaining the impact of compression on PCT free but could not find any useful ones. Can you please the relation between PCT Free and EHCC Compression.

and we said...

PCTFREE is implicitly zero on compressed tables (even just basic compression with HCC), as Oracle assumes if you are compressing the data then you are doing so for one or more of the following reasons:

a) To reclaim or safe space
b) Improve query performance
c) Minimal changes will occur on the data

Therefore changing the PCTFREE setting on the tablespace will not reclaim any space from compressed tables.

The only way to reduce the size of the EHCC tables would be to increase the level of compression to Capacity Low or Capacity High.

However, you should be aware that increasing the compression level will have some side effects. For example, you will need more CPU when you decompress the data.



Is this answer out of date? If it is, please let us know via a Comment