Archives of the TeradataForum
Message Posted: Mon, 15 Jan 2002 @ 05:46:48 GMT
If your intent is to identify the total number of unique BAN values there are common to all three tables, the following SQL should do the trick:
Select count(*) From (select a.ban from jq8274.bill_charge a, jq8274.bill_adjustment b, jq8274.tax c where a.ban = b.ban and a.ban = c.ban group by 1) as tmp_tbl
It's difficult to tell why you are running out of spool without knowing the size of the system and the size of the tables. 180 GB sounds like a lot of spool, but if your system is large, the spool per AMP might not be too large. If your system is 1000 AMPS (isn't the SBC system at least that large?) that translates to 180 MB per AMP. If the query is skewed, which might be possible with a count distinct, this could be the problem. An easy way to tell is too look at either, a) currentspool while the query is running, or better yet, b) clear peakspool prior to running the query and then check it after the query completes. AMPUsage can also be insightful since query with spool query nearly always result in skewed CPU on the same AMP.
Thomas F. Stanek
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|