My client recently suggested to use compression on all CHAR and VARCHAR fields like the below:
Customer_Name VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS,
Customer_id char(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS,
#Will the above statement compress only compress nulls??
However, i have read that TD 13 onwards, Nulls are automatically compressed even if you don't explicitly specify.
#I need to know how will it effect the performance on any queries being fired on these columns and also the impact on the table due to unnecessary compression.
#Also, do we need CHARACTER SET LATIN NOT CASESPECIFIC as this is the default value even if we dont specify.?
#1: Yes, It will compless NULLs only. (BTW NULLs are NOT automagically compressed without the COMPRESS clause).
#2: In general, shorter rows=>more rows per datablock=>less IO. I don't understand the term "unnecessary compression". Again, in general, the more COMPRESS, the better (with the right values, of course).
#3: It is defaulted. Dbscontrol fields DefaultCaseSpec and DefaultCharacterSet.
Adding to #3:
DefaultCaseSpec is only for string comparisons in Teradata mode sessions, but the default for a new column is based on the session mode,always NOT CASESPECIFIC in a Teradata session, but CASESPECIFIC in ANSI.
Beside the global DefaultCharacterSet there's also a user level DEFAULT CHARACTER SET to set Latin/Unicode as default.
I'm not sure what you meant with "always NOT CASESPECIFIC in a Teradata session".
In Teradata session mode (which is the 'usual' mode, an the mode I was assuming for the op) the DefaultCaseSpec rules the CASESPECIFIC mode of the new created columns.
If DefaultCaseSpec = TRUE in dbscontrol and you create a table with character columns defaulted, this columns will be CASESPECIFIC.
of course you're correct, DefaultCaseSpec also controls the default for new columns (I was under the impression this is only for comparisons, my fault).
Sorry Carlos, I couldn't get a chance to reply to your post. Thanks for your input.
By "unnecessary compression" i meant they have not specified any values for compression. Its just the compress keyword.
And Thank you Dieter for your valuable suggestions.