Archives of the TeradataForum
Message Posted: Fri, 27 Jul 2012 @ 14:06:41 GMT
| Subj: || || Re: PE plan for join |
| From: || || Dieter Noeth |
sughesh iyyani wrote:
| ||Suppose there are 2 tables, table a with 10 million records, table b has 100 million records, now we are joining both tables, when we see
explain plan of PE the plan showing TD will took smaller table a and it will redistributes it among all AMPs to do join.|| |
| ||My question is: by doing this plan is the optimizer is correct job or not?|| |
Table B seems to have the PI on the join column, table A has a different PI, so redistributing the small table seems reasonable.
| ||So much spool space required for this operation.|| |
Explain will show you how much spool the optimizer assumes (but it will not tell if it's evenly distributed or skewed)
| ||If I have UPI and some SI on this table, will PE select above plan?|| |
A SI on the join column will not change that plan (unless you join a small number of rows), because using a SI for that join would need more
resources. This is different to most other DBMSes.
But if the join column is the PI of table A you'll get a direct join without any preparation.