Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

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


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


Subj:   Re: How to identify the spool space of a particular
 
From:   Christie, Jon

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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020