Home DBMS SQL PL/SQL SQLite MongoDB Cassandra MySQL Oracle CouchDB Neo4j DB2 C Java
“I Am Very Gurug ram's
Comfortable Banjara
With Intimac… Market Is A…
Zee Zest ZeeZest.Com
“I Am Very Comfortable With
⇧ SCROLL TO TOP
Intimacy On Screen,” Says…
Sponsored Zee Zest
Nested-Loop Join Algorithm
In our previous sect ion, we learned about joins and various t ypes of joins. In t his
sect ion, we will know about t he nest ed loop join algorit hm.
A nest ed loop join is a join t hat cont ains a pair of nest ed for loops. To perform t he
nest ed loop join i.e., θ on t wo relat ions r and s, we use an algorit hm known as t he
Nested loop join algorithm. The comput at ion t akes place as:
r⋈θs
where r is known as t he outer relation and s is t he inner relation of t he join. It is
because t he for loop of r encloses t he for loop of s.
Nested-Loop Join Algorithm
ADVERTIS EMENT
This Is How Much Mumbai Maids Cha
To Clean A House In 2022
Search Ads
ADVERTIS EMENT
Let 's discuss t he algorit hm of nest ed-loop join:
for each tuple tr in r do begin
for each tuple ts in s do begin
test pair (tr, ts) to test if they satisfy the given join condition ?
if test satisfied
add tr . ts to the result;
end inner loop
end outer loop
In t he algorit hm, t r and t s are t he t uples of relat ions r and s, respect ively. The
not at ion t r. t s is a t uple const ruct ed by concat enat ing t he at t ribut e values of t uples
t r and t s.
Wit h t he help of t he algorit hm, we underst ood t he following point s:
The nest ed-loop join does not need any indexing similar t o a linear file scan for
accessing t he dat a.
Nest ed-loop join does not care about t he given join condit ion. It is suit able for
“I Am Very Comfortable With
each given join condit ion. Intimacy On Screen,” Says…
⇧ SCROLL TO TOP Sponsored Zee Zest
The nest ed-loop join algorit hm is expensive in nat ure. It is because it comput es
and examines each pair of t uples in t he given t wo relat ions.
Cost Analysis of Nested-loop Join Algorithm
For analyzing t he cost of t he nest ed-loop join algorit hm, consider a number of pairs of
t uples as nr * ns. Here, nr specifies t he number of t uples in relat ion r and ns specifies
t he number of t uples in relat ion s. For comput ing t he cost , perform a complet e scan
on relat ion s. Thus,
Tot al number of block t ransfers in worst case = nr * bs + br
Tot al number of seeks required in worst case = nr + br
Here, bs and br are t he number of blocks holding t he t uples of relat ion r and s,
respect ively.
In t he best case, bot h relat ions r and s have sufficient memory t o fit in t he memory
simult aneously. So, each block will read only once. Thus,
Tot al number of block t ransfers in best case = br + bs.
Tot al number of seeks required = 2(nr + br)
In case if any one of t he relat ions given fit s ent irely int o t he memory, it is a must t o
use t hat relat ion as t he inner relat ion. It is because we will read t he inner relat ion only
once. Thus,
Tot al number of block t ransfers in such case = br + bs
This Is How Much Mumbai Maids
Charge To Clean A House In 2022
Search Ads
Tot al number of seeks required = 2(nr + br)
Block Nested-Loop Join
“I Am Very Comfortable With
Intimacy On Screen,” Says…
⇧ SCROLL TO TOP Sponsored Zee Zest