 |
 |
Archives of the TeradataForum
Message Posted: Thu, 05 Nov 2009 @ 19:49:44 GMT
| Subj: | | Re: UPI not behaving as expected. |
| |
| From: | | Victor Sokovin |
| | 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.... | |
Strange indeed. I can only guess that the decision to replicate this table comes from the analysis of *other* joins that you mentioned. The
optimizer is trying to think a few steps ahead and seems to be willing to "sacrifice" the "easy" UPI because it probably sees other uses for the
replicated data anyway.
Try to experiment with reducing the number of tables and joins in the query to see for which join the plan changes.
Victor
| |
