How to simplify query that runs out of spool space?

Database

How to simplify query that runs out of spool space?

 hello,

i have a query that runs out of spool space as it performs a count on several joined tables (there are multiple 1 - Many relationships and one Many - Many relationship, which is the only way it can be joined based on avaialble tables and columns)

how can you go about resolving this issue? how would you rewrite it using a dervied table?

thank you all for your help!

stefan

general syntax of the query is below:

=========================================

select count(distinct CustomerID) as NoOfCustomers

from table1

join table2

    on table2.columnname = table1.columnname

join table3

    on table2.columnname = table3.columnname

join table4

    on table4.columnname = table1.columnname

join table5

    on table4.columnname = table5.columnname

where condition1

and condition2

and condition3

and condition4

and condition5

order by 1

10 REPLIES
Enthusiast

Re: How to simplify query that runs out of spool space?

These joins are quite simpler for Teradata. The things you can modify about these join is to make sure that the data types of joining columns are are same. Collecting statistcis will most probably avoid the out of spool issue. If still the issue persists, you can use derived or volatile tables.

Or you can write:

select count(distinct CustomerID) as NoOfCustomers

from
(
SELECT CustomerID
FROM table1
join table2
on table2.columnname = table1.columnname
join table3
on table2.columnname = table3.columnname
join table4
on table4.columnname = table1.columnname
join table5
on table4.columnname = table5.columnname
where condition1
and condition2
and condition3
and condition4
and condition5

)DRV

order by 1
Khurram

Re: How to simplify query that runs out of spool space?

hi Khurram,  all the data types of the joining columns are the same...i tried rewriting the query like the example above, but the result is the same Out of Spool space issue

Enthusiast

Re: How to simplify query that runs out of spool space?

Have you collected the required stats?

Khurram
Enthusiast

Re: How to simplify query that runs out of spool space?

Place the following before your query:

DIAGNOSTIC HELPSTATS ON FOR SESSION;
--Please your query here.

Now press F6, at the end of explain plan, you will get the required stats to be collected. 

From the explain plan you can also deduce why the query is going out of spool.

Khurram
Senior Apprentice

Re: How to simplify query that runs out of spool space?

A m:n-join might result in a huge intermediate spool causing the no more spool error.

Without knowledge about your data model it's impossible to tell exactly how to rewrite your query, but many rows per x might be reduced to one using a DISTINCT x in a Derived Table or a subquery instead of a join.

Dieter

Enthusiast

Re: How to simplify query that runs out of spool space?

Hi,

Why can't you post your explain here so that we can analyze , the demography, steps etc.

Cheers,

Raja

Re: How to simplify query that runs out of spool space?

thank you all for your replies, unfortunately I can't post details of the code due to confidentiality...but your input gave me some good ideas...thanks....i will come back if I have further questions

Re: How to simplify query that runs out of spool space?

hello everyone, just want to post a quick update...my issue has been resolved by redesigning the queries using subqeuries and derived tables...thank you for your input!

Re: How to simplify query that runs out of spool space?

Hi All, Can some one point me to a document which talks about the TD 14 version new additional SQL standards that improve the performance of the query. Either in terms of indexes, Partitions, or general SQL statements.