All Forums Database
prahladk 4 posts Joined 08/09
10 Sep 2009
Adding Compression to existing table

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

Ansh 49 posts Joined 10/08
10 Sep 2009

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);

prahladk 4 posts Joined 08/09
10 Sep 2009

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

Ansh 49 posts Joined 10/08
10 Sep 2009

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.

smilever 45 posts Joined 10/07
16 Sep 2009

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:-)

pawan0608 101 posts Joined 12/07
17 Sep 2009

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

ebarner 2 posts Joined 09/09
17 Sep 2009

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.

super25 19 posts Joined 07/11
31 Aug 2011

I think this should be fixed in 13.10, right?

NathanHagemann 7 posts Joined 05/10
10 May 2012

This is working in 13.10.  Works great in fact.

Karam 75 posts Joined 07/09
17 Aug 2012

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 

 

 

sanjeevs 1 post Joined 03/10
21 Nov 2012

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

You must sign in to leave a comment.