teradata

How to calculate available space in a Teradata database


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?


Solution

  • 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.