Value compression on timestamp column

Database
Enthusiast

Value compression on timestamp column

Hi all,
I want to know how to compress a column with timestamp(6) datatype.
Can anybody give the syntax for the same.

Thanks and Regards,
Aravind
13 REPLIES
Enthusiast

Re: Value compression on timestamp column

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.

Regds,
sachin
Enthusiast

Re: Value compression on timestamp column

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.

--Thanks
Aravind
Enthusiast

Re: Value compression on timestamp column

Hi Arvind,
If you have reference in Teradata documentation that timestamp column can be compressed, please send it to me.

Regds,
sachin
Enthusiast

Re: Value compression on timestamp column

Hi,Sachin,
Documentation says that Timestamp column can't be compressed.
But,I'v seen that null value compression is possible for timestamp column.

--Rgds,
Aravind
Enthusiast

Re: Value compression on timestamp column

Hi Arvind,
Even though the syntax for null compression on timestamp column is working
fine, check whether internally it is actually compressing by some mean.

Regds,
sachin

Enthusiast

Re: Value compression on timestamp column

Here's what I see in the documentation:

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

Re: Value compression on timestamp column

Hi,
I'v done null compression for timestamp. If multivalue compression is also possible, please let me know.

--thanks,
Aravind

Re: Value compression on timestamp column

Hi,

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.

Thanks,

M
Enthusiast

Re: Value compression on timestamp column

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.

--Regards,
Aravind