I want to report on various statistics on a specific Teradata database, particularly "free space". Should table skew be included in the calculation? For example, someone suggested the following query:
SELECT databasename
, SUM(maxperm)/1024/1024/1024 (DECIMAL(10,2)) AS space_allocated
, SUM(currentperm)/1024/1024/1024 (DECIMAL(10,2)) AS space_Used
, (MAX(currentperm)*COUNT(*)-SUM(currentperm))
/1024/1024/1024 (DECIMAL(10, 2)) AS skew_Size
, (space_used + skew_size) AS total_space_used
, (MIN(maxperm-currentperm)/1024/1024/1024) * COUNT(*) (DECIMAL(10,2))
AS free_Space
, CAST(total_space_used AS DECIMAL(10,2)) * 100
/ CAST(space_allocated AS DECIMAL(10,2)) AS pct_used
FROM DBC.diskspace
WHERE databasename = 'MyDatabase'
AND maxperm > 0
GROUP BY 1;
I'm particularly curious about the calculation of total_space_used
and pct_used
. Is it "proper" to account for skewed tables like this?
Definitely keep skewing into account. Your column free_space
gives you exactly the free space with skewing of all existing tables considered.
Also the free_space
assumes that all future tables are perfectly distributed. Without skewing.