Hi Arvind, In teradata only following data types can be compressed. • DATE (4) • CHAR (N) (N<256) • BYTEINT (1) • SMALLINT (2) • INTEGER (4) • FLOAT/REAL (8) • DOUBLE (8) • DECIMAL (1, 2, 4, or 8) • BYTE (N) (N<256)
Teradata doesn't support compression of timestamp(6) datatype.
Hi Sachin Thanks for the reply. One thing I'v noticed is I can compress null values for timestamp column. But Multi Value compression on timestamp column is what I wanted. If you get this one also,please reply.
Column compression is not supported for volatile tables or for the following types of columns: • Identity • VARCHAR • LONG VARCHAR • VARGRAPHIC • VARBYTE • BLOB • CLOB
And here's a table definition where I've done it:
CREATE SET TABLE production_tables.user_session_tool_access,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( Visitor_Id DECIMAL(15,0) NOT NULL, Visit_Num SMALLINT NOT NULL, Person_Client_Org_Web_rowguid CHAR(38) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, Tool_ID INTEGER NOT NULL, Tool_Start_Dtm TIMESTAMP(0) NOT NULL, Tool_End_Dtm TIMESTAMP(0) COMPRESS , Create_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL, Update_DT DATE FORMAT 'YYYY-MM-DD' COMPRESS , Load_Instance_ID INTEGER NOT NULL, CONSTRAINT usr_sess_tool_acc_usr_sess_fk FOREIGN KEY ( Visitor_Id , Visit_Num ,Person_Client_Org_Web_rowguid ) REFERENCES WITH NO CHECK OPTION PRODUCTION_TABLES.USER_SESSION ( Visitor_Id , Visit_Num ,Person_Client_Org_Web_rowguid ), CONSTRAINT usr_sess_tool_acc_tool_fk FOREIGN KEY ( Tool_ID ) REFERENCES WITH NO CHECK OPTION PRODUCTION_TABLES.TOOLS ( Tool_ID )) PRIMARY INDEX usr_sess_tool_acc_nuppi ( Visitor_Id ,Visit_Num ) PARTITION BY RANGE_N(CAST((tool_start_dtm ) AS DATE FORMAT 'YYYY-MM-DD') BETWEEN DATE '2005-01-01' AND DATE '2007-01-01' EACH INTERVAL '1' MONTH ) UNIQUE INDEX usr_sess_tool_acc_pk ( Visitor_Id ,Visit_Num ,Person_Client_Org_Web_rowguid , Tool_ID );
I consult with the R&D organization on priorities for future compression features. Can you provide a use case for value list timestamp compression. I think the general impression is that timestamps would not occur frequently and therefore would not usually benefit from value compression.
Hi, Its difficult for me to give a use case for this.But in our data warehousing environment,we keep track of the load time of target tables as a timestamp column.I wanted to compress the timestamp and save the space since we will be loading the tables usually twice in a month.