Archives of the TeradataForum

Message Posted: Mon, 26 Mar 2007 @ 20:35:35 GMT

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

It's best to avoid the DBC.AllSpace view unless you're very, very sure what you're doing. Use DBC.DiskSpace and DBC.TableSize instead.

The trouble with DBC.AllSpace is that it includes both the summary (database/user) level rows from DBC.DatabaseSpace and the detail (table) level rows. This can lead to confusing and seemingly incorrect results.

For instance:

     sel databasename
        ,sum(currentperm)
     from DBC.AllSpace
     group by 1 having sum(currentperm) > 0
     order by 2 desc;

will return TWICE each user or database's current perm because it sums both the detail and summary rows.

This will return the desired result and, on most systems, will run faster.

     sel databasename
        ,sum(currentperm)
     from DBC.DiskSpace
     group by 1 having sum(currentperm) > 0
     order by 2 desc;

If all you're interested in is spool, stick to DBC.DiskSpace which selects only summary (user/database) rows. Spool is only recorded at the database level, so bringing in all the detail rows, when you use AllSpace, only to have them sum to zero is a waste of time.

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