All Forums Database
aravind-3019 39 posts Joined 08/06
28 Dec 2006
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

sachinp17 53 posts Joined 11/06
29 Dec 2006

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

aravind-3019 39 posts Joined 08/06
30 Dec 2006

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

sachinp17 53 posts Joined 11/06
02 Jan 2007

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

aravind-3019 39 posts Joined 08/06
02 Jan 2007

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

sachinp17 53 posts Joined 11/06
03 Jan 2007

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

jklee 40 posts Joined 07/06
08 Jan 2007

Here's what I see in the documentation:Column compression is not supported for volatile tables or for the following types ofcolumns:• Identity• VARCHAR• LONG VARCHAR• VARGRAPHIC• VARBYTE• BLOB• CLOBAnd 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 );

aravind-3019 39 posts Joined 08/06
11 Jan 2007

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

TechPurism 2 posts Joined 01/07
13 Jan 2007

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

aravind-3019 39 posts Joined 08/06
19 Jan 2007

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

CFranke 2 posts Joined 10/07
09 Oct 2007

I would like to offer as a use case for timestamp compression: a trace and lot component analysis System. Consider: "On a specific date and time, how much XX product is located in area Z." When did component depart Area Q and arrive Area R.

CFranke 2 posts Joined 10/07
09 Oct 2007

I would like to offer as a use case for timestamp compression: a trace and lot component analysis System. Consider: "On a specific date and time, how much XX product is located in area Z." When did component depart Area Q and arrive Area R.

RobG 2 posts Joined 09/10
08 Apr 2011

TechPurism, you asked if someone could "provide a use case for value list timestamp compression".

We are using a specific timestamp to indicate "end of time". This is consistent with what is in the new PERIOD datatype. In this case, all "open" records have the same timestamp value for valid end time.

Allowing compression here would save significant space.

dnoeth 4628 posts Joined 11/04
08 Apr 2011

If there's both start and end timestamp switching to Period in TD13 will save disk space, too.
UNTIL_CHANGED is stored in a single byte.

Dieter

Dieter

You must sign in to leave a comment.