How to find the skew factor for a Teradata table

When Teradata tables are not distributed evenly this can cause significant performance problems. Storage space is also not utilized properly. For that reason identifying and monitoring tables that are not distributed evenly across AMPs is very important when working with Teradata databases. The following query will reveal the skew factor for the SALES and CUSTOMERS tables from a Teradata table.

SELECT
   DatabaseName,
   TableName,
   SUM(CurrentPerm) AS CurrentPerm ,
   SUM(PeakPerm) AS PeakPerm,
   (100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100)) AS SkewFactor
  FROM DBC.TableSize
 WHERE databasename =  database
   AND TableName IN ('SALES' , 'CUSTOMERS')
 GROUP BY 1,2
 ORDER BY SkewFactor DESC;

To fix skew problems, you need to change the table’s primary index.

For an empty table, you can use ALTER TABLE:

ALTER TABLE orders MODIFY PRIMARY INDEX (ORDER_NUM);

For already populated table you might need to recreate the table. You can use the following sequence:

RENAME TABLE orders TO orders_tmp;
-- Step 1. Create an empty table
CREATE TABLE orders_tmp AS (SELECT * FROM orders) WITH NO DATA;

-- Step 2. Alter the primary index for the new empty table
ALTER TABLE orders_tmp MODIFY PRIMARY INDEX (ORDER_NUM);

-- Step 3. Copy data from the skewed table
INSERT INTO orders_tmp SELECT * FROM orders;

-- Step 4. Remove the old table and rename the new table
RENAME TABLE orders TO orders_skewed;
RENAME TABLE orders_tmp TO orders;

You might want to keep the old table for a while and after you are sure you are not going to need it, drop it:

DROP TABLE orders_skewed;
About these ads
This entry was posted in Teradata and tagged . Bookmark the permalink.

One Response to How to find the skew factor for a Teradata table

  1. venki says:

    Hi Thanks for query which is very nice to know the skewness, i need some changes in a query where i need total no.of rows in table in the same query. my database consist of 700 tables

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s