Archives of the TeradataForum

Message Posted: Fri, 07 Jul 2006 @ 17:50:12 GMT

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

First check if stats are collected on the join columns and refreshed lately. Also check how much spool space is required to run the query by trying in another userid(usually admin id) which has more than 50GB. First, clear the PeakSpoolSpace to 0 in dbc.databasespace, run your SQL and then run the following query. Second value tells you the total spool consumed by the SQL and this is the required spool space for this query. You should have proper privileges to your userid to access DBC tables.

     sel sum(s.peakspool) (format 'zzz,zzz,zzz,zz9') (CHAR(15))   ,
            max(s.peakspool)*hashamp() (format 'zzz,zzz,zzz,zz9') (CHAR(15))

        from dbc.diskspace s    where s.databasename=user;

If there is a drastic difference between these 2 values, the data is skewed. To find the skewed AMP spool usage, run this query. Compare with the skewness on join columns of the tables (using the HASH functions). This will give you a general idea on what is skewed.

     sel max(peakspool), vproc
     from dbc.diskspace where databasename = user
     group by 2
     order by 1 desc;

This is a very simple query with a join of 2 tables and I don't see a issue with the optimizer plan. You may also try creating some secondary indexes too.

Hope this helps!

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