 |
 |
Archives of the TeradataForum
Message Posted: Thu, 24 Jun 2004 @ 17:06:15 GMT
| Subj: | | Re: Duplicate Table across all AMPs |
| |
| From: | | Matthew Winter |
Hi,
| | 1) We've found that putting a small table on a single AMP (via a NUPI on a constant value which can be referenced in a view of the base
table), in most cases, will encourage the optimizer to duplicate a small table to all AMPs. | |
We have tried this approach in the past with no luck. It seems as if 9 times out of 10, the optimiser decides to perform a redistribution of
the small table back to how it would have been before placing it onto a single AMP.
| | 2) If this doesn't work, depending on your situation, you may want to consider collecting stats on the small table while it is empty,
then populate the table. This approach works fine when only a few tables are involved; however, if many tables are involved, this probably won't
be a safe approach. | |
Never tried this one. We have always ensured the statistics are as accurate as possible. I will certainly try this approach.
| | 3) Finally, make sure that the hash join feature is enabled (for star schemas, this is a must.) This will result in fewer
redistributions of the LT. | |
Has been since our system went live.
It would however be far simpler and more of a direct prompt to the optimiser, if a feature exists to set a table as duplicated.
Thanks Claybourne, your comments are most welcome.
Regards
Matthew Winter
| |
