Compression for tables with more than 250 columns

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

Comments:

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'.

Posted by Paul Alsemgeest on August 03, 2010 at 01:36 AM IDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

bocadmin_ww

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today