 |
 |
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? | |
Probably.
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.
Dieter
| |
