sliding window merge join

Database
Enthusiast

sliding window merge join

Hi,

Can somebody pls help me to understand sliding indow merge join.I know it happne for PPI to No PPI table or both PPI but joining on different column.I want to understand process.

6 REPLIES
Enthusiast

Re: sliding window merge join

Yo could have read the design doc :)

A Direct Merge Join cannot be used when one table is partitioned and the other is not, or

when both tables are partitioned, but not partitioned identically, because the rows of the two

tables are not ordered in the same way. The Sliding Window Merge Join, which is PPI-aware,

can be invoked by the Optimizer to cover situations that the standard Direct Merge Join

cannot. Sliding window joins can be slower than a Merge Join or Rowkey-Based Merge Join

when there are many noneliminated combined partitions; however, a Sliding Window Merge

Join can provide roughly similar elapsed time performance (but with greater CPU utilization

and memory consumption) when the number of noneliminated combined partitions is small.

Sliding Window Merge Joins follow the general principle of structuring each of the left and

right relations into windows of appropriate

10

sizes. The join is done as a Product Join between

each of these left and right window pairs. The operation uses the identical algorithm to that

used for a regular Merge Join within each window pair with the exception that the rows are

not necessarily in row hash order across the multiple partitions within a window. 

........................................................................

..........

I just hope a modeler can avoid landing situation :)

and above that you can see the single window merge join, with examples on partition and join.

Enthusiast

Re: sliding window merge join

:( All this I have read but still its not getting inside my head.What is want to understand the initernal processing.I got when it can occur and what are Pros and Cos.I am interested in How part.

Teradata Employee

Re: sliding window merge join

Hello Meenakshi,

The following link contains a forum post concerning optimization involving Sliding Window Merge Join:

http://forums.teradata.com/forum/database/help-on-tuning-a-query#comment-138104

In Layman terms, both the Left & Right relations are partitioned into appropriate sizes. Then, product join operation between the partitions is performed. If Table_1 has 5 Partitions & Table_2 has 7 Partitions, we have 5*7 = 35 Product Joins Operations performed for completing the Join Operation. During the Join Operation, the PPICacheThrP field decides the maximum number of partitions from both tables that can be brought from Disk to Memory for performing the Join Operation.

Thanks,

Smarak

Enthusiast

Re: sliding window merge join

Sliding Window merge join is PPI aware, is it always true? if both the tables in join are NPPI table, then is there any possibility of Sliding Window?

Senior Apprentice

Re: sliding window merge join

Sliding Window Join is applied only to partitioned tables, without partitioning there's no need for it. 

Re: sliding window merge join

What is the procedure to Avoid Sliding Window Join? Can we use a Diagnostic Cost profile? If So How ?

Is there any system impact in doing so ?