Adding Compression to existing table

Database

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.

regards

Prahlad
10 REPLIES
Enthusiast

Re: Adding Compression to existing table

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

Re: Adding Compression to existing table

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
Enthusiast

Re: Adding Compression to existing table

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.
Enthusiast

Re: Adding Compression to existing table

Hi Prahlad,Ansh

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

Re: Adding Compression to existing table

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

Re: Adding Compression to existing table

There is a DR out Advising against using ALTER to compress, be aware of your system patch level.

DR/JIRA/Defect Tracking ID:

DR 128641

Fixed Release(s)/Platform:
TDBMS_6.2.2.74
TDBMS_12.0.2.15
TDBMS_13.0.0.3

Workaround:
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.
Enthusiast

Re: Adding Compression to existing table

I think this should be fixed in 13.10, right?

Enthusiast

Re: Adding Compression to existing table

This is working in 13.10.  Works great in fact.

Enthusiast

Re: Adding Compression to existing table

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