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.
Yo could have read the design doc :)
:( 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.
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.
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?
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 ?