Archives of the TeradataForum

Message Posted: Wed, 16 Jul 2003 @ 16:32:11 GMT

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

We are in the process of going through most of the production tables and compressing the columns that provide a perm space savings. Up to now we have compressed approx. 20 tables with over a Terabyte of perm space savings. We have found that not only does compression save space, but also queries run faster.

All we did for testing was create the new compressed table in another database and loaded it and ran a few queries against it just to see the performance difference between the uncompressed.

We have come across only one small issue so far.

If a create-table-as is used and one of the columns in the select is a compressed column in a base table being used, then that column will also be compressed in the new table being created. This might cause a bit of overhead to the new table especially if the value being compressed is not a highly used value. The issue is an error can occur if in your create-table-as, you change the primary index to one of the compressed columns in the base table being used. The error is a 3623 = The user can not compress on a primary index field.

The fix is to cast the compressed column(s) causing the error in the create-table-as to the same data type they are in the base table but leaving out the compress.

An Example;

CREATE TABLE wits.test,
NO FALLBACK,
NO JOURNAL AS
(
SELECT
a.sid,
a.acct_class,
a.rate_plan
FROM prodv.v_tableA a
GROUP BY 1,2,3
) WITH DATA
PRIMARY INDEX (sid, acct_class, rate_plan);

ERROR 3623: The user cannot use compress on a primary index field.

TableA acct_class has been compressed. Since that column is in the Primary Index of the new table the 3623 error will occur.

The Fix:

CREATE TABLE wits.test,
NO FALLBACK,
NO JOURNAL AS
(
SELECT
a.sid,
CAST(a.acct_class AS CHAR(2) ) AS acct_class,
a.rate_plan
FROM prodv.tableA a
GROUP BY 1,2,3
) WITH DATA
PRIMARY INDEX (sid, acct_class, rate_plan);

I really don't see the need of a complete round of testing for every application. It is pretty simple to back out the compression if any issues arise although I don't believe that will happen. If you are ultra conservative you could implement one compressed table at a time.

Stephen DBA

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