Archives of the TeradataForum

Message Posted: Wed, 30 Jul 2003 @ 10:55:51 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Aniruddha,

if you look at the explains for the query not running out of spool you can see that it first joins the 2 lookup tables for district & country. Then the result of joining the two fact tables is joined to this.

In the query running out of spool you do everything step by step & join to the country table last. I would guess that you are getting a hot amp situation there. What is going to happen is that TD is redistributing all the other data by country-id. So if you have one country-id in your data everything is going to sit on one AMP.

I'm not quite sure that the outer join compared to the inner join are the reason for the different explain plans you are seeing. You mention in your original email that you can't collect statistics on all of the tables, as some of the are created by MSTR on the fly. This means the optimizer will do random AMP sampling. Maybe it just choose different Amps with different data population & got the different explains for that reason?

You could check if statistics are available for the lookup tables. These are not going to change on the fly. It's very important to have proper stats on the lookups, as they can be quite small & random sampling can lead to huge problems.

Hope this helps!

Mirjam

	<Prev	Next>		<<First	<Prev	Next>	Last>>