15.00 - Different Disk Space Views Return Different Results - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Different Disk Space Views Return Different Results

It is important to understand that because the DBC.AllSpaceV, DBC.DiskSpaceV, and DBC.TableSizeV system views report different information, using superficially identical requests to return information using those views often returns conflicting results. For example, while DBC.AllSpaceV and DBC.DiskSpaceV include information about spool space in their reports, DBC.TableSizeV does not.

Similarly, selecting SUM(CurrentPerm) from each of these views returns different results. For example, requesting SUM(CurrentPerm) from DBC.AllSpaceV returns a sum of All “tables,” representing the database or user total plus the sum of each table in the database or user. Submitting the same request using DBC.TableSizeV returns sums for all tables except for All, and submitting the query using DBC.DiskSpaceV returns sums only for All.

The following requests illustrate the different results the same request returns for the same information, but using different system views to access the data.

  • DBC.AllSpaceV
  •      SELECT MAX(CurrentPerm), SUM(CurrentPerm)
         FROM DBC.AllSpaceV
         WHERE DatabaseName = user;

     

         Maximum(CurrentPerm)      Sum(CurrentPerm)
         --------------------      ----------------
                      112,640             1,308,672
     

    The reported values represent the disk space for all of the tables contained by user plus the disk space for the All table.

  • DBC.DiskSpaceV
  •      SELECT MAX(CurrentPerm), SUM(CurrentPerm)
         FROM DBC.DiskSpaceV
         WHERE DatabaseName = user;

     

         Maximum(CurrentPerm)      Sum(CurrentPerm)
         --------------------      ----------------
                      112,640               654,336
     

    The reported values represent the disk space for the All table.

  • DBC.TableSizeV
  •      SELECT MAX(CurrentPerm), SUM(CurrentPerm)
         FROM DBC.TableSizeV
         WHERE DatabaseName = user;

     

         Maximum(CurrentPerm)      Sum(CurrentPerm)
         --------------------      ----------------
                       71,680               654,336
     

    The reported values represent the disk space for all of the tables contained by user, but do not include the All table.

    Use the following table to determine which system views are appropriate for requesting disk space information for different database objects.

     

    To report disk space information at this level …

    You should use this system view …

    table

    DBC.TableSizeV

    database, user, or profile

    DBC.DiskSpaceV

    tables plus database, user, or profile

    DBC.AllSpaceV