If you have to look for skew in Teradata tables in a particular database, one way to do it is to use the Teradata Administrator visual tool and right-click a table name and select “Space Summary.” That’s good for inspecting a few tables (less than 10 to 20), but if you have hundreds of tables in database that you need to inspect that method can get quite cumbersome.
Below is SQL to list all the tables in a specified database and rank by skew factor high to low. The higher the number (between 0 and 100) the worse the data in the table is skewed. A skew of 100 means data exists only on 1 AMP (unit of parallelism). A skew of 0 means data is perfectly distributed amongst all the AMPS on the system. For the Teradata System to perform optimally you want to get as even distribution across all the units of parallelism (AMPs) as you can. You will hardly see perfect distribution in the real world, but you can get really, really close to it. Anything under a skew factor of 10 should be OK, but your mileage may vary.
You can issue the SQL below in BTEQ, SQL Assistant, or any other tool you have connected to a Teradata database. Copy the SQL and replace the PUT_YOUR_DATABASE_NAME_HERE with the name of the database you wish to inspect and remember to keep the ‘single quotes’ around the database name when issuing the SQL.
Select
DatabaseName
,Tablename
,SUM(CurrentPerm) AS CurrentPerm
,SUM(PeakPerm) AS PeakPerm
, 100-(AVG(CurrentPerm)/MAX(CurrentPerm)*100) AS Skewfactor
From
DBC.TableSize
Where
DatabaseName = 'PUT_YOUR_DATABASE_NAME_HERE'
Group By
1,2
Order BY
Skewfactor desc
;
James T. Scoggins
