Use of the AllSpaceV View - Analytics Database - Teradata Vantage

The DBC.AllSpaceV view provides space usage information at the object level (table, join index, permanent journal, or stored procedures) and the database/user level. However, Teradata recommends using the DBC.DiskSpaceV and DBC.TableSizeV views instead because DBC.AllSpaceV can return misleading results.

For example, the following query returns the current permanent space from the detail rows and summary rows for each user or database. Note that the view sums data from both types of rows:

SELECT DatabaseName
       ,Sum(CurrentPerm)
       FROM DBC.AllSpaceV
       GROUP BY 1 having sum(currentperm) > 0
       ORDER BY 2 desc;

Result:

DatabaseName                       Sum(CurrentPerm)
------------------------------  -------------------
DBC                                      47,183,872
Sys_Calendar                              2,648,064
SysAdmin                                  1,380,352
SYSLIB                                      319,488
SystemFe                                    148,480

However, a similar query using DBC.DiskSpaceV will return the desired result and, on most systems, will run faster:

SELECT DatabaseName
       ,Sum(CurrentPerm)
       FROM DBC.DiskSpaceV
       GROUP BY 1 having sum(currentperm) > 0
       ORDER BY 2 desc;

Result:

DatabaseName                       Sum(CurrentPerm)
------------------------------  -------------------
DBC                                      23,591,936
Sys_Calendar                              1,324,032
SysAdmin                                    690,176
SYSLIB                                      159,744
SystemFe                                     74,240

The view DBC.DiskSpaceV includes only summary rows and the view DBC.TableSizeV includes only detail rows. The view DBC.AllSpaceV, however, includes both summary and detail rows and when you sum currentperm from AllSpaceV, you get the sum from both the summary and detail rows added together.

If you want space usage information at the database/user level, use the DBC.DiskSpaceV view which selects only summary (user/database) rows. If you want space usage information at the object level, use the DBC.TableSizeV view.