With playOrm we have been researching partitioning and joining partitions for OLTP applications which you typically partition per client anyways such that you can have infinite clients. Naturally, we have been looking at a lot of nested loop join, block nested loop join, sort merge join, and hash join.
We just discovered a new one that is truly nice in the noSQL world. We call it the lookahead nested loop join. It is one step better than the nested block join because data arrives before you end up at the top of the loop again. While you are looping over one batch, the next batch is being fetch(ie. Lookahead nested loop join). We plan on incorporating that optimization into playOrm and testing joins in playOrm vs. hibernate joins on an RDBMS with 100k's of rows in a PARTITION (100k's of rows in a table for the hibernate test) to see how it pans out. We may scale the test up to a join of 1,000,000 rows with 500k rows as well(not sure how far we will push it yet). If you are interested, let me know and I can send you results of our join tests. Dean
