Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 05 Jan 2001 @ 16:31:56 GMT


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


Subj:   Re: Union Conundrum
 
From:   Jeremy Christiansen

Thanks to all who replied. Thanks especially to Dave for the reference to the spool size calculations. I am still baffled, though. The problem has nothing to do with join plans or statistics. When I ran the two selects individually I was effectively running amp steps #4 and #5 (from the explain in my last note) by themselves. That is, I isolated each of the main steps in the explain and then ran them individually. I of course did explains on these individual queries and saw that the optimizer was doing the same thing. That is, when I ran select #1 (which corresponds to amp step #4) by itself, the optimizer generated the same access path as when I ran the select as part of the "union all" query. The same is true for select #2 (which corresponds to amp step #5). Furthermore, I ran all queries using the same tool. To reiterate my findings, I know for a fact that the answer sets produced by the two select statements (when run individually) totaled 2.3 and 25 gig respectively. I took those numbers from dbc.diskspace after the queries finished. (I used Queryman to do this. When Teradata finishes its work it sends data to Queryman. After 2000 rows have been sent Queryman pauses and asks if you want to receive more data. This gives me time to query dbc.diskspace to find out exactly how much spool I am using.) Yet for some reason when the two selects are unioned they take over 50 gig of spool. Bizarre. (By the way, I use a "union all" in order to avoid the overhead of duplicate row elimination--due to the nature of what I'm doing, it is impossible for the query to generate duplicate rows.)

By the way, I have already rewritten the query in manner that circumvents this difficulty. I just wish I knew why the union query behaved as it did.

p.s. If anyone is interested in writing/sharing obfuscated Teradata SQL, please email me personally. It's a fun way to exercise your mind as well as learn about esoteric features of the parser. And hey, you might learn something useful!



     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023