Archives of the TeradataForum

Message Posted: Tue, 28 Mar 2006 @ 11:21:53 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Here is the content of KA S11000C100A: ALLSPACE vs DISKSPACE/TABLESIZE views described

-dave.clark

------------------------------------------------------- The DiskSpace and TableSize views are all anyone needs to query space utilization. Any query using the AllSpace view could better be written using one of the other views. In fact, the AllSpace view can actually return misleading results. For instance, the query:

     Select sum(currentperm) from DBC.AllSpace;

will return TWICE the amount of space currently occupied by permanent tables.

To understand why, one has to understand that there are two distinct types of rows in DBC.DatabaseSpace. One type of row holds space usage information about a single permanent table. (To be completely accurate, a single permanent table, join index, permanent journal, or stored procedure, but it's easier just to say 'permanent table' so that's what I'll do from now on.)

The other kind of row holds space usage information about a database or user. These two types of rows are easily distinguished from one another. In the database/user rows, TableId is zero. In table rows, TableId is non-zero and contains the table identifier of the table the row contains information about. I think of the database/user rows as summary rows and the table rows as detail rows.

Every amp has exactly the same set of rows in the DatabaseSpace table. There is one summary row for each database and each user, and there is one detail row for each permanent table. The space information in the rows will vary from amp to amp, but every amp will have exactly the same set of rows.

The view DBC.DiskSpace includes only summary rows. (Note the term TableId = '000000000000'xb.) The view DBC.TableSize includes only detail rows. (Note the term TableId <> '000000000000'xb.) The view DBC.AllSpace includes both types of rows (no term to eliminate rows). This is why the query above returns twice what one would expect. When you sum currentperm, you get the sum from both the summary and detail rows added together.

If you examine the TableSize view, you will see that there are only two columns with space information: CurrentPerm and PeakPerm. This is because all the other columns with space information are zero in detail rows. Why is this? Because the max columns represent limits that are applied at the database/user level (in other words, the summary level), and spool usage information is maintained only at the user (summary) level. Temporary space usage is tracked at both the summary and detail level, but the detail rows do not show up in the TableSize view because the TableId column in temporary detail rows matches DBC.TempTables instead of DBC.TVM.

There is no reason to use the AllSpace view. As pointed out, it will return twice the desired number for CurrentPerm, so there is actually a very good reason NOT to use the AllSpace view.

	<Prev	Next>		<<First	<Prev	Next>	Last>>