Observing Exadata HCC compression changes when adding columns

This blog posting is very much a follow on from the previous entry on how data compressed with Exadata HCC compression behaves under changing table definitions. Many thanks to Greg Rahn for the comments on the previous blog entry on a simple mechanism for determining whether the compression level has changed or not.

In this blog posting we add a column to an HCC compressed table and we observe whether the number of blocks in the table changes or not.

As Greg stated in the comments on the previous blog entry, we have 3 possibilities for adding a column:

  1. add column
  2. add column with a default value
  3. add column with a default value but also specify as not null

We start with the same table as in the previous entry:

SQL : db01> create table t_part (
username varchar2(30),
user_id number,
created date )
partition by range (created)
(partition p_2009 values less than (to_date('31-DEC-2009', 'dd-MON-YYYY')) tablespace users,
partition p_2010 values less than (to_date('31-DEC-2010', 'dd-MON-YYYY')) tablespace users,
partition p_2011 values less than (to_date('31-DEC-2011', 'dd-MON-YYYY')) tablespace users,
partition p_2012 values less than (to_date('31-DEC-2012', 'dd-MON-YYYY')) tablespace users )

/

Table created.

SQL : db01> alter table t_part compress for query high

/

Table altered.
SQL : db01> insert /*+ APPEND */ into t_part select * from all_users

488 rows created.

SQL : db01> commit;

Commit complete.

So now we gather stats on the table and see how many blocks the table is consuming:

SQL : db01> exec DBMS_STATS.gather_table_stats(ownname => 'SYS',tabname => 'T_PART', estimate_percent => 100);
PL/SQL procedure successfully completed.

SQL : db01> select table_name, blocks, empty_blocks, avg_row_len , last_analyzed from dba_tables where table_name='T_PART';

TABLE_NAME BLOCKS EMPTY_BLOCKS   AVG_ROW_LEN LAST_ANAL
---------- ------ -------------- ---------- ------------
T_PART        60      0          20         18-MAY-12

This will be our starting point for each of the 3 ways of adding a column. We will always start with this table consuming 60 blocks. We will then add the column and then determine how many blocks the table is consuming after the column has been added.

If the table has undergone decompression from HCC compression the number of blocks will go up, conversely if it has not then the number of blocks will remain static.

First we try just adding a column, no default value:

SQL : db01> alter table t_part add city varchar2(30);

Table altered.

SQL : db01> exec DBMS_STATS.gather_table_stats(ownname => 'SYS', tabname => 'T_PART', estimate_percent => 100);

PL/SQL procedure successfully completed.
SQL : db01> select table_name, blocks, empty_blocks, avg_row_len , last_analyzed from dba_tables where table_name='T_PART';

TABLE_NAME BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANAL
---------- ------ ----------  ---------- ------------
T_PART        60      0          20         18-MAY-12

So this method has not updated the number of blocks. It’s just a dictionary change. We then drop the table with the purge option and recreate it back to the starting point of 60 blocks. Next we try adding the column with a default value:

SQL : db01> alter table t_part add city varchar2(30) default 'Oxford';
Table altered.
SQL : db01> exec DBMS_STATS.gather_table_stats(ownname => 'SYS', tabname => 'T_PART', estimate_percent => 100);

PL/SQL procedure successfully completed.
SQL : db01>select table_name, blocks, empty_blocks, avg_row_len , last_analyzed from dba_tables where table_name='T_PART';

TABLE_NAME   BLOCKS  EMPTY_BLOCKS  AVG_ROW_LEN  LAST_ANAL
------------ ------ ------------    ---------- -----------
T_PART        192       0             27       18-MAY-12

As we can see the number has absolutely rocketed up from 60 to 192, this is indicative of the fact the data is no longer compressed with HCC compression.

Finally we repeat adding a column with a default value, but this time including the not null condition:


SQL :  db01> alter table t_part add city varchar2(30) default 'Oxford' not null;

Table altered.

SQL :  db01>  exec DBMS_STATS.gather_table_stats(ownname => 'SYS', tabname => 'T_PART', estimate_percent => 100);

PL/SQL procedure successfully completed.
<pre>SQL : db01> select table_name, blocks, empty_blocks, avg_row_len , last_analyzed from dba_tables where table_name='T_PART';

TABLE_NAME BLOCKS EMPTY_BLOCKS   AVG_ROW_LEN LAST_ANAL
---------- ------ -------------- ---------- ------------
T_PART        60      0          20         18-MAY-12

We see that with thetechnique of including a not null clause on the add column with a default value that the number of blocks has not changed, and hence the data must still be HCC compressed, as confirmed with the DBMS_COMPRESSION.GET_COMPRESSION_TYPE procedure.

Essentially if you can have any column that you add to an HCC compressed table to be defined as  not null  then you can be sure that specifying a default value will not undo your HCC compression.

If you do need to allow nulls, then getting away without a default value would be best and perhaps only updating recent data rather than all historical data would at least preserve some data as being HCC compressed. Be aware that uncompressing HCC compressed obviously can lead to a large increase in your storage requirements.

About these ads
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 58 other followers

%d bloggers like this: