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;