Oracle Exadata HCC Removes the 255 Columns Limit

It is a little known fact that Oracle table compression doesn’t work with tables that have more than 255. It’s has been reported and compression limitations in general here.

As a follow up from my previous post on Exadata Design, where I question the use of dimensions for certain attributes in data warehouses, I figured I should test whether HCC works with tables that have more than 255 columns.

And it does. Here’s my test case.

Setup:

[sql] create table ctest (
col_1 varchar2(30) default ‘JUSTSOMEDATA’,
col_2 varchar2(30) default ‘JUSTSOMEDATA’,
…
col_255 varchar2(30) default ‘JUSTSOMEDATA’
);
insert into ctest (col_1) select ‘OTHERDATA’ from dba_objects;
insert /*+APPEND*/ into ctest select * from ctest;
commit;
insert /*+APPEND*/ into ctest select * from ctest;
commit;
[/sql]

Build various scenarios (NOTE: second create adds the 256th column):

[sql] /*REGULAR*/
create table ctest_cmp compress as select * from ctest;
create table ctest_cmp1 compress as select t.*, ‘NEW’ col_256 from ctest t;

/*HCC QUERY*/
create table ctest_hcc compress for query low as select * from ctest;
create table ctest_hcc1 compress for query low as select t.*, ‘NEW’ col_256 from ctest t;

/*HCC ARCHIVE*/
create table ctest_hccA compress for archive low as select * from ctest;
create table ctest_hccA1 compress for archive low as select t.*, ‘NEW’ col_256 from ctest t;
[/sql]

And the results:

[sql] SQL> select round((bytes)/1024/1024) as mb , segment_name from user_segments t where segment_name like ‘CTEST%’ order by segment_name;

MB SEGMENT_NAME
———- —————————-
824 CTEST
3 CTEST_CMP
824 CTEST_CMP1
1 CTEST_HCC
1 CTEST_HCC1
1 CTEST_HCCA
1 CTEST_HCCA1
[/sql]

Want to talk with an expert? Schedule a call with our team to get the conversation started.