|
|
| Line 1: |
Line 1: |
| {{multiple issues|
| | The title of the author is Jayson. Her family lives in Alaska but her spouse desires them to move. For years she's been operating as a travel agent. As a lady what she really likes is fashion and she's been performing it for fairly a while.<br><br>Feel free to visit my weblog; [http://srncomm.com/blog/2014/08/25/relieve-that-stress-find-a-new-hobby/ accurate psychic predictions] |
| {{no footnotes|date=October 2012}}
| |
| {{refimprove|date=November 2006}}
| |
| }}
| |
| A '''block-nested loop''' ('''BNL''') is an [[algorithm]] used to [[join (SQL)|join]] two relations in a [[relational database]].
| |
| | |
| This algorithm is a variation on the simple [[nested loop join]] used to join two relations <math>R</math> and <math>S</math> (the "outer" and "inner" join operands, respectively). Suppose <math>|R| < |S|</math>. In a traditional nested loop join, <math>S</math> will be scanned once for every tuple of <math>R</math>. If there are many qualifying <math>R</math> tuples, and particularly if there is no applicable index for the join key on <math>S</math>, this operation will be very expensive.
| |
| | |
| The block nested loop join algorithm improves on the simple nested loop join by only scanning <math>S</math> once for every ''group'' of <math>R</math> tuples. For example, one variant of the block nested loop join reads an entire [[page (computing)|page]] of <math>R</math> tuples into memory and loads them into a [[hash table]]. It then scans <math>S</math>, and probes the hash table to find <math>S</math> tuples that match any of the tuples in the current page of <math>R</math>. This reduces the number of scans of <math>S</math> that are necessary.
| |
| | |
| A more aggressive variant of this algorithm loads as many pages of <math>R</math> as can be fit in the available memory, loading all such tuples into a hash table, and then repeatedly scans <math>S</math>. This further reduces the number of scans of <math>S</math> that are necessary. In fact, this algorithm is essentially a special-case of the classic [[hash join]] algorithm.
| |
| | |
| The block nested loop runs in <math>O(P_r P_s/M)</math> I/Os where <math>M</math> is the number of available pages of internal memory and <math>P_r</math> and <math>P_s</math> is size of <math>R</math> and <math>S</math> respectively in pages. Note
| |
| that block nested loop runs in <math>O(P_r+P_s)</math> I/Os if <math>R</math> fits in the available internal memory.
| |
| | |
| ==References==
| |
| {{Reflist}}
| |
| * [http://dev.mysql.com/doc/refman/5.6/en/bnl-bka-optimization.html Block Nested-Loop Joins] in the MySQL 5.6 Reference Manual.
| |
| | |
| {{DEFAULTSORT:Block Nested Loop}}
| |
| [[Category:Join algorithms]]
| |
Latest revision as of 18:16, 1 April 2014
The title of the author is Jayson. Her family lives in Alaska but her spouse desires them to move. For years she's been operating as a travel agent. As a lady what she really likes is fashion and she's been performing it for fairly a while.
Feel free to visit my weblog; accurate psychic predictions