Hi,You can use the ALTER command to add or modify the table columns and add compression information to it.For example :ALTER TABLENAME ADD columnname datatype compress (Values);
Hi Ansh, Thanks for the reply. However this will work only when the table is empty. But I do not have that liberty.Is there any way where we can add more compression values to a table which contains data, without dropping the table?thanks Prahlad
Hi ,Alter does not support compression on existing tables.In such a case, there is no other option but to add a new column with compress attribute.Update all the values in old column to new column then remove old column and rename new column name to old column name.
Hi Prahlad,AnshWe can compress the values in a column using alter statement even for the populated table.I had checked it..ALTER TABLE tablename ADD columnname COMPRESS(values);Cheers:-)
You can add new columns with COMPRESS clause in existing table having data with ALTER TABLE statement, but can't add/modify COMPRESS clause of an existing column
There is a DR out Advising against using ALTER to compress, be aware of your system patch level.DR/JIRA/Defect Tracking ID:DR 128641Fixed Release(s)/Platform:TDBMS_6.2.2.74TDBMS_12.0.2.15TDBMS_13.0.0.3Workaround:When table needs to be altered, instead of altering the table create a new table and then perform an insert/select from the original table to a new table with the desired attributes in place of the ALTER TABLE operation.
This is working in 13.10. Works great in fact.
It works with TD 12.00 also.
Here's a testing script..
--Adding compression on existing column
----------------------------------------
SEL * FROM DBC.DBCINFO;
*** Query completed. 3 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
InfoKey InfoData
------------------------------ --------------------------------------------
RELEASE 12.00.02.33
VERSION 12.00.02.33
LANGUAGE SUPPORT MODE Standard
+---------+---------+---------+---------+---------+---------+---------+----
CREATE SET TABLE DBA_TEST_DB.TEST_COMPRESSION
(
column_a BYTEINT,
column_b DECIMAL(15,2), --COMPRESS 0.00 ,
column_c DECIMAL(15,2) --COMPRESS 0.00 ,
)
UNIQUE PRIMARY INDEX ( column_a);
*** Table has been created.
*** Total elapsed time was 1 second.
+---------+---------+---------+---------+---------+---------+---------+----
INSERT INTO DBA_TEST_DB .TEST_COMPRESSION
VALUES( 5,10.2,11.5);
*** Insert completed. One row added.
*** Total elapsed time was 1 second.
+---------+---------+---------+---------+---------+---------+---------+----
SEL * FROM DBA_TEST_DB.TEST_COMPRESSION;
*** Query completed. One row found. 3 columns returned.
*** Total elapsed time was 1 second.
column_a column_b column_c
-------- ----------------- -----------------
5 10.20 11.50
+---------+---------+---------+---------+---------+---------+---------+----
ALTER TABLE DBA_TEST_DB.TEST_COMPRESSION
ADD column_b DECIMAL(15,2) COMPRESS 0.00
,ADD column_C DECIMAL(15,2) COMPRESS 0.00 ;
*** Table has been modified.
*** Total elapsed time was 2 seconds.
+---------+---------+---------+---------+---------+---------+---------+----
SHOW TABLE DBA_TEST_DB.TEST_COMPRESSION;
*** Text of DDL statement returned.
*** Total elapsed time was 1 second.
---------------------------------------------------------------------------
CREATE SET TABLE DBA_TEST_DB.TEST_COMPRESSION ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
column_a BYTEINT,
column_b DECIMAL(15,2) COMPRESS 0.00 ,
column_c DECIMAL(15,2) COMPRESS 0.00 )
UNIQUE PRIMARY INDEX ( column_a );
+---------+---------+---------+---------+---------+---------+---------+----
INSERT INTO DBA_TEST_DB .TEST_COMPRESSION
VALUES( 6,0.00,0.00);
*** Insert completed. One row added.
*** Total elapsed time was 1 second.
+---------+---------+---------+---------+---------+---------+---------+----
SEL * FROM DBA_TEST_DB.TEST_COMPRESSION;
*** Query completed. 2 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
column_a column_b column_c
-------- ----------------- -----------------
6 .00 .00
5 10.20 11.50
+---------+---------+---------+---------+---------+---------+---------+----
.IF ERRORCODE <> 0 THEN .GOTO ErrorExit
+---------+---------+---------+---------+---------+---------+---------+----
.LABEL ERROREXIT
+---------+---------+---------+---------+---------+---------+---------+----
.REMARK "ERROR: BTEQ_Wrapper.bteq Job Failed"
ERROR: BTEQ_Wrapper.bteq Job Failed
+---------+---------+---------+---------+---------+---------+---------+----
.quit;
*** You are now logged off from the DBC.
*** Exiting BTEQ...
*** RC (return code) = 0
And if you want to add more values to the existing Compression List, you can do it as under :
ALTER TABLE DBA_TEST_DB.TEST_COMPRESSION
ADD column_b DECIMAL(15,2) COMPRESS ( 0.00, 0.20 )
,ADD column_C DECIMAL(15,2) COMPRESS (0.00, 0.30 );
=> remember to include the existing values also with this command else they will be overwritten with new values.
--Cheers
Hi, Has Teradata come up with a function to add compression values to the existing table columns' without dropping and recreating the table.Any update in this regard would be highly appreciated.regardsPrahlad