CODEWITHSUNDEEP
teradata
anwaar_hell
anwaar_hell

Reputation: 776

query is getting spool out in teradata

I am executing below queries , but one query is giving me resultand other is giving me [Error 2646] [SQLState HY000] No more spool space in USER.

 SELECT DISTINCT PARTITION
    FROM  DB.TABLE 
     ORDER BY PARTITION  ASC;

Error:-[Error 2646] [SQLState HY000] No more spool space in USER.

But when i am executing it is giving me results:-

 select * from (
 SELECT DISTINCT PARTITION
    FROM  DB."TABLE") x
    ORDER BY X.PARTITION ASC;

Upvotes: 0

Views: 1018

Answers (1)

dnoeth
dnoeth

Reputation: 60482

Teradata's optimizer usually checks if a DISTINCT can be rewritten using GROUP BY (and vice versa).

In your 1st query it choose distinct processing (which is redistribution followed by a sort) because of the ORDER BY (of course this is stupid).

Derived Tables using DISTINCT will not be fwolded, i.e. the optimizer will materialize it. Without the DISTINCT it applies the aggregate rewrite which does a AMP-local aggregation as 1st step greatly reducing spool usage.

If you add COUNT(*) to #1 it will not spool out and return useful information :)

On the other hand, if I wanted to know about partitions with data I would query dbc.Stats...

Upvotes: 3

Related Questions