Can some please explain me Sliding Window and Single Window Merge Join?
This is what i have understood when reading the Statement and Transaction Processing Reference Manual...
1) This Join Strategy is used when Rowkey based Merge join is not possible.
2) Single window is used when total non-eliminated partitions are less and Sliding window when it is not
3) The Strategy involves creating windows (datablocks read into Memory) and join is decomposed into multiple number of
subjoins either between windows or between a window and another relation.
I still dont get a concrete explanation of Sliding window and Single window Merge Join...
some of my questions are
1) will the records within window be sorted by Rowkey value?
2) wouldnt there be multiple passes (reading datablock) be made on the table in which the window was not created in sliding window join?
When a sliding window merge join is done, the rows are not sorted beforehand. They are in the order that they appear in the table (by partition, then by the hashcode).
If you have one table/spool that is not windowed and the other that is windowed in the sliding window merge join, then a pass would have to be made through the non-windowed side for every window on the windowed side. So, if the windowed side has five windows, then five passes would be made through the non-windowed side.
Note that a "window" can be multiple partitions and is based on the amount of memory used for the operation. So, if ten partitions from the "windowed" table can fit into the window and there are a total of 50 patitions to be joined, then Teradata will make five passes of the non-windowed table.
The difference between the sliding window merge join and the single window merge join is that in the single window merge join, all of the partitions from the "windowed" side or sides (in the case that both sides are windowed) fit into a single window.