 |
 |
Archives of the TeradataForum
Message Posted: Fri, 07 Jul 2006 @ 20:35:25 GMT
| Subj: | | Re: No more spool for user |
| |
| From: | | Stover, Terry |
It looks like you have a problem with statistics, I can't recall ever seeing a query where every step has a no confidence estimate. Collect
stats on each of the PI columns individually, and on the composite PI. You also need to collect stats on the join column. Collect the stats and
see how that changes the explain. You may want to declare your foreign key columns as not null, that should get rid of the ("NOT
(tmp_sfe.productos_considerados_1.cd_party IS NULL)") steps, although I haven't really noticed that they impact performance significantly.
You might try setting the PI for both tables to be cd_party so you can get an amp local join, especially since you are joining using a between
qualifyer. From the name, the column cd_producto_instancia doesn't sound very unique. In general, a column should have many more distinct values
than the number of amps on your system, otherwise it doesn't help much with data distribution. You could also try making the
tmp_productos_considerados_1 PI non-unique, even though it is unique. Teradata occasionally has an irrational desire to maintain UPI's in spool
files. A value ordered nusi on tmp_productos_considerados_1 cd_party might help if you can't change the PI.
One thing with spool limits, they are enforced by amp. If the user has a 20 gig spool limit and your system has 20 amps, you have a max of 1
gig per amp. With the merge join the data is being distributed to the amps based on cd_party. If some of the cd_party values have a high row
count you are probably spooling out on the join.
| |
