A block-nested loop (BNL) is an
algorithm
In mathematics and computer science, an algorithm () is a finite sequence of rigorous instructions, typically used to solve a class of specific problems or to perform a computation. Algorithms are used as specifications for performing ...
used to
join two relations in a
relational database
A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relatio ...
.
This algorithm
is a variation of the simple
nested loop join
A nested loop join is a naive algorithm that joins two sets by using two nested loops. Join operations are important for database management.
Algorithm
Two relations R and S are joined as follows:
algorithm nested_loop_join is
for each tupl ...
and joins two
relations
Relation or relations may refer to:
General uses
*International relations, the study of interconnection of politics, economics, and law on a global level
*Interpersonal relationship, association or acquaintance between two or more people
*Public ...
and
(the "outer" and "inner" join operands, respectively). Suppose
. In a traditional nested loop join,
will be scanned once for every
tuple
In mathematics, a tuple is a finite ordered list (sequence) of elements. An -tuple is a sequence (or ordered list) of elements, where is a non-negative integer. There is only one 0-tuple, referred to as ''the empty tuple''. An -tuple is defi ...
of
. If there are many qualifying
tuples, and particularly if there is no applicable
index
Index (or its plural form indices) may refer to:
Arts, entertainment, and media Fictional entities
* Index (''A Certain Magical Index''), a character in the light novel series ''A Certain Magical Index''
* The Index, an item on a Halo megastru ...
for the join key on
, this operation will be very expensive.
The block nested loop join algorithm improves on the simple nested loop join by only scanning
once for every ''group'' of
tuples. Here groups are
disjoint sets
In mathematics, two sets are said to be disjoint sets if they have no element in common. Equivalently, two disjoint sets are sets whose intersection is the empty set.. For example, and are ''disjoint sets,'' while and are not disjoint. ...
of tuples in
and the
union
Union commonly refers to:
* Trade union, an organization of workers
* Union (set theory), in mathematics, a fundamental operation on sets
Union may also refer to:
Arts and entertainment
Music
* Union (band), an American rock group
** ''Un ...
of all groups has the same tuples as
. For example, one variant of the block nested loop join reads an entire
page
Page most commonly refers to:
* Page (paper), one side of a leaf of paper, as in a book
Page, PAGE, pages, or paging may also refer to:
Roles
* Page (assistance occupation), a professional occupation
* Page (servant), traditionally a young ma ...
of
tuples into
memory
Memory is the faculty of the mind by which data or information is encoded, stored, and retrieved when needed. It is the retention of information over time for the purpose of influencing future action. If past events could not be remembered ...
and loads them into a
hash table
In computing, a hash table, also known as hash map, is a data structure that implements an associative array or dictionary. It is an abstract data type that maps keys to values. A hash table uses a hash function to compute an ''index'', ...
. It then scans
, and probes the hash table to find
tuples that match any of the tuples in the current page of
. This reduces the number of scans of
that are necessary.
algorithm block_nested_loop_join is
for each page ''pr'' in ''R'' do
for each page ''ps'' in ''S'' do
for each tuple ''r'' in ''pr'' do
for each tuple ''s'' in ''ps'' do
if ''r'' and ''s'' satisfy the join condition then
yield tuple <''r'',''s''>
A more aggressive variant of this algorithm loads as many pages of
as can be fit in the available memory, loading all such tuples into a hash table, and then repeatedly scans
. This further reduces the number of scans of
that are necessary. In fact, this algorithm is essentially a special-case of the classic
hash join algorithm.
The block nested loop runs in
I/Os where
is the number of available pages of internal memory and
and
is size of
and
respectively in pages. Note
that block nested loop runs in
I/Os if
fits in the available internal memory.
References
{{DEFAULTSORT:Block Nested Loop
Join algorithms