Alejandro Vargas' Blog

  • March 1, 2010

Compression for tables with more than 250 columns

Alejandro Vargas
Technical Leader, ACS Global Delivery, Infrastructure & BigData

Compression for tables with more than 250 columns

Tables with more than 250 columns are not supported to be compressed, this restriction remains in place even on 11g R2.

On the 11g R2, Sql Language Reference Manual, page 16-36 we can read:

Restrictions on Table Compression

* COMPRESS FOR OLTP and COMPRESS BASIC are not supported for tables with more than 255 columns.

This is a serious limitation specially for Telecoms where CDR tables can have a number of columns way over 255.

The available workaround:

  • Split the table into 2 sub-tables.
  • create table A as select pk,field 1 to 150 from origtable

  • create table B as select pk,field 151 to 300 from origtable

  • Each one will have less than 250 rows.

  • They will be joined by the primary key.

  • The table will be accessed using a view that has all the columns of the original table.
  • create view origtable as select a.pk,field a.1 to a.150, field b.151 to b.300 from a, b where a.pk=b.pk

Join the discussion

Comments ( 1 )
  • Paul Alsemgeest Monday, August 2, 2010
    Sounds easy, but what about indexes spanning both tables? It seems to me that these should be avoided by carefully choosing which columns go where.
    Also, when you don't need any data from table 'B' in a particular select statement, you better select from 'A' than from view 'v_AB'.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.