Archives of the TeradataForum

Message Posted: Mon, 15 Jan 2002 @ 05:46:48 GMT

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

Jay,

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
TFS Consulting
www.tfsconsulting.com

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