![](//teradataforum.com/imgs/ptdfw1.gif) |
![](//teradataforum.com/imgs/ptdfw2.gif) |
Archives of the TeradataForum
Message Posted: Thu, 05 Nov 2009 @ 12:33:47 GMT
Subj: | | UPI not behaving as expected. |
|
From: | | Anomy Anom |
<-- Anonymously Posted: Thursday, November 05, 2009 07:16 -->
I have a query which have inner joins on many tables, one of the inner join is quite a mystry to me so I just thought to share with you and
know if we can resolve this mystry.
A table is have a single-column UPI and this table is being joined with other table on UPI when I see the explain plan, it tells that this
particular join be being performed using merge join. Even though there is UPI on this table still this table is being duplicated on all the 400
AMPs and then merge join is performed..due to this duplication this step is taking over 19 min. Here I can not see the advantage of UPI with
uptodate stats. Can some one please explain as to why this is happening and can I avoid it? Below is exact copy of explain plan step....
1) We do an all-AMPs RETRIEVE step from
in view
by way of an all-rows
scan with no residual conditions into Spool 15 (all_amps)
(compressed columns allowed), which is duplicated on all AMPs.
Then we do a SORT to order Spool 15 by the hash code of (
). The
result spool file will not be cached in memory. The size of
Spool 15 is estimated with high confidence to be
3,928,867,200 rows (184,656,758,400 bytes). The estimated
time for this step is 19 minutes and 24 seconds.
Regards
Anom
|
| |
|
|
|
|
| |
| |
|
|
Copyright 2016 - All Rights Reserved |
Last Modified: 15 Jun 2023 |
|
|