Archives of the TeradataForum

Message Posted: Tue, 04 Feb 2003 @ 16:23:08 GMT

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

There are several factors to consider. They have to be considered in light of several other factors as well. Here are the most obvious rules:

1. Varchar columns cannot be compressed

2. Varchar columns require two-bytes of overhead per row to indicate the column's actual length

3. Char columns can be compressed but only when not part of the PI or identity columns

Also, there are some other restrictions on the use of varchar and compression on some index types. I can't note them all here in this email.

The trick is finding out which data type (and attributes) are most optimal in terms of perm space savings and query resource consumption. Sometimes, fixed length CHAR is optimal, other times CHAR with compression, and sometimes VARCHAR.

Generally, the more bytes you can squeeze out of each column, more rows fit in block, and therefore less I/O is required. This results in queries running faster and some benefit to CPU consumption. Applying compression, efficient data types and lengths over all columns of a table can have a significant impact on resource consumption and query performance.

I've done some extensive testing to find out where the break-even point might be and developing a rules-based optimization process to take all of these issues into account. The Database Design document discusses some of these issues in more detail.

I trust this helps.

Doug Drake

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