I am very new to teradata and have TD query as:
CREATE SET TABLE tablename(
fieldname DECIMAL(15,2) COMPRESS (0.00 ,50.00 ,46.00 )
What the value will be entered in this fieldname if i insert 1000.594 in the table?
What is the difference between values of DECIMAL(15,2) and DECIMAL(15,2) COMPRESS (0.00 ,50.00 ,46.00 ) ?
COMPRESSION is a teradata feature which allows the users to compress the most commonly appearing values in once column. Physically, the compressed values are stored in table header and not reapeating in each row thus saving some space. If a value other than the compressed list comes with a row, that takes its normal allocated space...
COMPRESS (.....) clause defines the values that are compressed.
In your case only 0.00, 50.00 and 46.00 are the values that will be compressed, so if you enter 1000.594 it is not compressed but consumes all the allocated bytes.
Yep ,as Qaisar said,If the compress the column values which they are repeating and keep those columns as compress,so we can save some space .We would observe the saved space ,If it is a huge data or there more repeating column values exist.
Here nothing much difference , DECIMAL(15,2) are not compressing or not saving space and DECIMAL(15,2) COMPRESS (0.00 ,50.00 ,46.00 ) are compressing with repeated columns or are saving some space,Thats all
Can you let me know what is the checksum and checksum default which is specified while creating a table.What is the use of it and how does it perform.
Is there a way to define a range ? I have a table create that does compress on 5 date fields like
below and there are 105 dates for each date filed ...any way to get around not having to hard code all values ?
opn_dt DATE FORMAT 'YYYY-MM-DD' COMPRESS
(DATE '1994-08-01',DATE '2001-12-01',DATE '2003-04-01',
DATE '2006-01-01',DATE '1988-09-01',DATE '1990-01-01',
DATE '1997-05-01',DATE '2004-09-01',DATE '2000-02-01',
DATE '2007-06-01',DATE '1991-06-01',DATE '1998-10-01',
DATE '1992-11-01',DATE '1994-03-01',DATE '2001-07-01',
DATE '2008-11-01',DATE '1988-04-01',DATE '1995-08-01', etc
Not when using Multi-Value Compression (MVC) which is what is being used in your example. You have to code each value.
Algorithmic Compression (ALC) would allow you to compress a known rage of date values, but those would have to be hard-coded in your ALC routine - which you would have to write.
If you're requirement is to use MVC against a known range of dates then you could write some SQL which would build the list of values for you. Then you simply cut'n'paste the build list into your table definition.
SELECT ',DATE'''||(BEGIN(date_range) (FORMAT 'yyyy-mm-dd'))||'''' AS thedate FROM dbc.databasesv WHERE databasename = USER EXPAND ON PERIOD( DATE'2017-01-01',DATE'2017-02-01' ) AS date_range BY INTERVAL '1' DAY
This example produces the below list of date values which can be inserted into a 'create table' statement.
,DATE'2017-01-01' ,DATE'2017-01-02' ,DATE'2017-01-03' ,DATE'2017-01-04' ,DATE'2017-01-05' ,DATE'2017-01-06' ,DATE'2017-01-07' ,DATE'2017-01-08' ,DATE'2017-01-09' ,DATE'2017-01-10' ,DATE'2017-01-11' ,DATE'2017-01-12' ,DATE'2017-01-13' ,DATE'2017-01-14' ,DATE'2017-01-15' ,DATE'2017-01-16' ,DATE'2017-01-17' ,DATE'2017-01-18' ,DATE'2017-01-19' ,DATE'2017-01-20' ,DATE'2017-01-21' ,DATE'2017-01-22' ,DATE'2017-01-23' ,DATE'2017-01-24' ,DATE'2017-01-25' ,DATE'2017-01-26' ,DATE'2017-01-27' ,DATE'2017-01-28' ,DATE'2017-01-29' ,DATE'2017-01-30' ,DATE'2017-01-31'
Manually remove the first ',' character.
I don't but there are examples in the 'sql external programming' manual. Try this: https://info.teradata.com/HTMLPubs/DB_TTU_15_10/index.html#page/SQL_Reference/B035_1147_151K/UDF_Cod...