All Forums Database
ramjasmeenakshi 19 posts Joined 04/11
16 Nov 2014
sliding window merge join

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.

Meenakshi Swami
Raja_KT 1246 posts Joined 07/09
17 Nov 2014

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


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.



Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

ramjasmeenakshi 19 posts Joined 04/11
19 Nov 2014

:( 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.

Meenakshi Swami

SmarakDas 51 posts Joined 02/12
24 Nov 2014

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

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.


agts 5 posts Joined 03/13
14 Mar 2015

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?

dnoeth 4628 posts Joined 11/04
16 Mar 2015

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


sg0890603 2 posts Joined 02/10
29 Apr 2015

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 ?

You must sign in to leave a comment.