Can you please help me concerning this matter (I didn´t found it in the Teradata documentation, which is honestly little overwhelming): My table has this column -BAN DECIMAL(9,0)-, and now I want to change it to - BAN DECIMAL(15,0) COMPRESS 0.- How can I do it?
What does COMPRESS constraint 0. or any other mean anyway?
I hope BAN DECIMAL(9,0) to - BAN DECIMAL(15,0) is possible, and I don`t have to create a new table and then copy the data form the old table. The table is very very big - when I do COUNT(*) form that table I get this error: 2616 numeric overflow occurred during computation
You have to create a new table and copy the data for this change. Some types of changes can be done with ALTER TABLE, but not DECIMAL(9) to DECIMAL(15).
COMPRESS 0 means that if the value in the column is zero, a flag will be set in the rowheader and the 8 byte field that would otherwise hold the value will be omitted - so the storage format of the row on disk will be shorter. The number of "presence bits" taken up in every rowheader depends on how many values you choose to compress - one bit for one value, two bits for 2 or 3 values, three bits for 4 to 7 values, etc. up to 255 values in 8 bits.
BTW - you can CAST(COUNT(*) AS BIGINT) or as DECIMAL(15) or (18) to avoid the numeric overflow.
If the column is not part of an index you can also try the following approach of adding a new column, update the new column and drop / rename columns
create table a_a (a integer, b decimal(8,0));
alter table a_a add b_new decimal(15,0) compress(0);
update a_a set b_new = b;
alter table a_a
rename b_new to b;
show table a_a;
drop table a_a;