Archives of the TeradataForum

Message Posted: Tue, 14 Feb 2006 @ 21:54:26 GMT

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

Hello Alan,

We're also on 5.1.2.something.

And we have also noticed, that sometimes it makes a huge difference whether the DPE is against all partitions or only a (small) fixed number of partitions.

That's the reason why we use the code-generation trick I described in an earlier post.

But, we must remember, that when we see the phrase "enhanced by dynamic partition elimination", it's a product join.

So it's Product Join Dynamic Partition Elimination - this is clarified in the latest version of the PPI Orange Book.

In general a product join is very CPU consuming, and this may be the reason for the variances in run-time.

Do you use Priority Scheduler with milestones?

This might make things "look worse", I suspect.

Well, this probably can't explain the differences.

So I'd be very happy if someone in the know, would clarify what exactly goes on, when product join DPE takes place against all partitions.

Are the "non-qualifying" partitions "probed" some how?

Back to your quest for "enforcing best performance".

What's the PI of the big_table, and what's the partitioning expression?

There might be other options than trying to force a product join DPE.

PS: I've never tried the case with say partition_date BETWEEN '2006-01-01' and DATE - 7.

This gives a very weird estimate of the number of partitions on my box!

Also, why couldn't the optimizer just substitute the DATE with its value at parse-time? I've heard something like concerns as if the DATE changed between parse-time and run-time. But why not use this simple definition?

Well, hope this helps somehow

Ole Dunweber
Coop Nordic

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