Archives of the TeradataForum

Message Posted: Fri, 06 May 2005 @ 14:18:06 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Shaju Kodian wrote:

          > '*** Failure 5708 Table header size exceeds limit.'

There are several issues with your table:

All 87 columns are NULLable, so it's not a relational table, because it can't have a Primary Key. And you'll need 11 bytes for presence bits per row. So check which columns are NOT NULL.

Most of the columns are varchar and each varchar has two bytes overhead:

5 * varchar(1) -> 10 bytes overhead

17 * varchar(2) -> even if the average length is 1 there's still 17 bytes overhead, if it's 2 the overhead is 34 bytes

So check which columns really have a variable length and an average length at least 2 bytes less than max length and change all other columns to CHAR.

And check if those columns are really characters...

Finally the partitioning: Always try to keep it simple, because the optimizer must be able to calculate the partition number from the WHERE- clause.

Make the Dcpbl column (YYYYMM?) an integer and use a RANGE partition:

     PARTITION BY RANGE_N
     (Dcpbl between
         200001 and 200012 each 1,
         200101 and 200112 each 1,
         200201 and 200212 each 1,
         200301 and 200312 each 1,
         200401 and 200412 each 1,
         200501 and 200512 each 1
     )

This will reduce the header size from 97kb down to 6kb :-)

Dieter

	<Prev	Next>		<<First	<Prev	Next>	Last>>