Tuesday, September 6, 2011

Query to find skew factor of a particular table ?

SELECT
TABLENAME
,SUM(CURRENTPERM) /1024/1024 AS CURRENTPERM,
(100 - (AVG(CURRENTPERM)/MAX(CURRENTPERM)*100)) AS SKEWFACTOR
FROM
DBC.TABLESIZE
WHERE DATABASENAME= <DATABASENAME>
AND
TABLENAME =<TABLENAME> 
GROUP BY 1;

What is the acceptable range for skew factor in a table?


There is no particular range for skew factor.  In case of production systems, it is suggested to keep skew factor between 5-10. 
There are various considerations for skew factor
- Number of AMPS
- Size of row in a table
- number of records in a table
- PI of a table
- Frequent access of table (Performance consideration)
- whether table getting loaded daily /monthly or how frequently data is being refreshed

What is multivalued compression in Teradata?

Multivalued compression or just MVC is a compression technique applied on columns in Teradata .  MVC has a unique feature of compressing up-to 255 distinct values per column in a given table. 

The advantage of compression are
  • Reduced Storage cost by storing more of a logical data than physical data.
  • Performance is greatly improves due to  reduced retrieval of physical data for that column.   
  • Tables having compression always have an advantage since optimizer considers reduced I/O as one of the factors for generating EXPLAIN plan.