Resolving skew in database
A database can run out of space if it has excessive tables that are skewed. Skew in small tables is understandable and can't be controlled, whereas skew in big tables can be controlled and reduced.
Note
DBC.TableSize: Provides space by AMP about table space usage
for each AMP.
Getting ready
We will connect to our Teradata database instance and open SLQA to generate the list of tables with their space and skew details.
How to do it...
- Pick the database on which you need to analyze the skew tables.
- Execute this query to get the list of tables:
/**Skew table in a database query**/ Lock Dbc.TableSize For Access Lock Dbc.tables For Access SELECT B.databasename , B.TableName , A.LastAccessTimeStAMP , SUM ( currentperm ) ( NAMED CurrentPerm ) , MAXIMUM ( currentperm ) ( NAMED MaxPerm ) , AVG ( currentperm ) ( NAMED AvgPerm ) , ( ( MAXIMUM ( currentperm ) - AVG ( currentperm ) ) * 100.0 ) / ( MAXIMUM ( currentperm ) ) ( NAMED SkewPercent ) FROM dbc.tablesize B INNER JOIN...
