 |
 |
Archives of the TeradataForum
Message Posted: Fri, 05 Jan 2001 @ 16:31:56 GMT
| 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!
| |
