### What is join operation, join of relations in dbms

What do you mean by Join operation in dbms? What are the algorithms used for computing join of relations? What is equi-join? What are the respective costs in computing join of relations? If you are looking for these questions, I can help.

 Learn Join Operation

In today's post, I will tell you about the join operation and how it works in dbms. So let's begin-

## What is a join operation?

Join is a combination of a Cartesian product followed by a selection process. A Join operation pairs two couples from different relations, if and only if a given join condition is satisfied.

We use the term equi-join to refer to a join, where A and B are attributes or sets of attributes of relations r and s, respectively.

Let's take a example of a expression: student x takes

We assume the following information about the two relations:

• Number of records of student: n(student) = 5,000.
• Number of blocks of student: b(student) = 100.
• Number of records of takes: n(takes) = 10,000.
• Number of blocks of takes: b(takes) = 400.

### Types of Join-operations:

There are various types of join-operations in data base management system. Let's try to understand them one by one.

#### Nested-Loop Join

Image below shows a straightforward formula to cypher the theta join , r ⋈c s, of two relations r and s . This algorithm is named the nested - loop join algorithm , since it essentially consists of a combine of nested for loops .
 Nested-loop join

Relation r is named the outer relation and relation s the inner relation of the join , since the loop for r encloses the loop for s . The formula uses the notation tr .ts , where tr and ts are tuples ; tr.ts denotes the tuple created by concatenating the attribute values of tuples tr , and ts .

Just like the linear file - scan formula for choice , the nested - loop join algorithm needs no indices , and it is used in spite of what the join's condition is . Extending the formula to cypher the natural join is simple , since the natural join is expressed as a theta join followed by elimination of recurrent attributes by a projection .

The sole modification needed is an additional step of deleting recurrent attributes from the tuple tr.ts , before adding it to the result .

The nested - loop join algorithm is pricey , since it examines each pair of tuples within the 2 relations. Take into account the value of the nested - loop join algorithm . The quantity of pairs of tuples to be thought of is nr * ns , where nr , denotes the quantity of tuples in r , and ns denotes the quantity of tuples in s .

For every record in r , we've got to perform a whole scan on s . Within the worst case , the buffer will hold just one block of every relation , and a complete of nr * bs + br , block transfers would be needed , wherever by br and bs denote the quantity of blocks containing tuples of r and s , severally .

We'd like just one search for every scan on the inner relation s since it is read consecutively , and a complete of  br , seeks to read r , resulting in a complete of nr + br , seeks .

Within the best case , there's enough area for each relations to suit at the same time in memory , thus every block would have to be compelled to be read one time ; therefore , solely br + bs , block transfers would be needed , together with two seeks .

If one among the relations fits entirely in main memory , it's helpful to use that relation because of the inner relation , since the inner relation would then be read just one occasion . Therefore , if s is little enough to suit in main memory , our strategy needs solely a complete br + bs block transfers and a pair of seeks -- constant value as that for the case wherever each relations slot in memory .

Now think about the natural join of student and takes . Assume for currently that we've no indices whatever on either relation , which we tend to aren't willing to make any index . We are able to use the nested loops to compute the join ; assume that student is that the outer relation and takes is that the inner relation within the join .

We'll got to examine 5000 * 10,000 = 50 * 10(6) pairs of tuples . Within the worst case , the quantity of block transfers is 5000 * 400 + 100 = 2,000,100 , and 5000 + 100 = 5100 seeks . Within the best case situation , however , we are able to read each relations just one occasion , and perform the computation .

This computation needs at the most 100+ 400 = 500 block transfers , and two seeks - a major improvement over the worst - case situation . If we had used takes because the relation was the outer loop and student for the inner loop , the worst - case value of our final strategy would have been  10,000 * 100 + 400 = 1,000,400 block transfers , plus 10,400 disk seeks .

#### Block Nested-Loop Join

If the buffer is just too little to carry either relation entirely in memory , we will still get a significant saving in block accesses if we tend to method the relations on a per - block basis , instead of on a per - tuple basis.

Image below shows block nested - loop join , which is a variant of the nested - loop join wherever each block of the inner relation is paired with each block of the outer relation .
 Block Nested-loop join

At intervals every combine of blocks , each tuple in one block is paired with each tuple within the different block , to come up with all pairs of tuples . As before , all pairs of tuples that satisfy the join condition are additional to the result .

The first distinction is in price between the block nested - loop join and therefore the basic nested - loop join is that , within the worst case , every block within the inner relations is read just the once for every block within the outer relation , rather than once for every tuple within the outer relation .

Thus , within the worst case , there'll be a complete of br * bs + br block transfers , wherever br and bs , denote the quantity of blocks containing records of r and s , severally . Every scan of the inner relation needs one seek , and also the scan of the outer relation needs one seek per block , resulting in a complete of 2 * br seeks .

Clearly , it's additionally economical to use the smaller relation because the outer relation , just in case neither of the relations fits in memory . Within the best case , wherever the inner relation fits in memory , there'll be br + bs block transfers and simply two seeks ( we might opt for the smaller relation because of the inner relation during this case ) .

Now come to our example of computing student x takes , using the block nested - loop join algorithm. Within the worst case , we've got to read every block of takes once for every block of student . Thus , within the worst case , a complete of 100 * 400 + 100 = 40,100 block transfers and 2 * 100 = 200 seeks ar needed .

This value could be a vital improvement over the 5000 *  4400 + 100 = 2,000,100 block transfers and 5100 seeks required within the worst case for the fundamental nested - loop join . The most effective - case value remains constant - particularly , 100 + 400 = 500 block transfers and a couple of seeks.

The performance of the nested - loop associated with the block nested - loop procedures can be further improved :
• If the join attributes in a very natural join or an equi - join form a key on the inner relation , then for every outer relation tuple the inner loop will terminate as before long because the initial match is found.
• Within the block nested - loop algorithm , rather than using disk blocks as the blocking unit for the outer relation , we are able to use the largest size which will slot in memory , whereas deed enough house for the buffers of the inner relation and also the output .
In different words , if memory has M blocks , we read in M - 2 blocks of the outer                    relation at a  time , and when we read every block of the inner relation we join it with              all the M - 2 blocks of the outer relation .

This variation reduces the quantity of scans of the inner relation from br to [b /(M- 2)] ,         where br is the range of blocks of the outer relation . The full value is then [br/(M - 2)              * bs +  b , block transfers and a 2[ br / ( M - 2 ) seeks .

• We are able to scan the inner loop alternately forward and backward . This scanning technique orders the requests for disk blocks so the info remaining within the buffer from the previous scan are often reused , so reducing the quantity of disk accesses required .
• If an index is obtainable on the inner loop's join attribute , we are able to replace file scans with additional economical index lookups.

#### Indexed Nested-Loop Join

In a nested-loop join, if an index is available on the inner loop's join attribute, index lookups can replace filescans. For each tuple tr in the outer relation r, the index is used to lookup tuples in s that will satisfy the join condition with tuple tr.

This join technique is termed an indexed nested - loop join ; it is used with purpose of existing indices , moreover like temporary indices created for the only real  purpose of evaluating the join .

Looking up tuples in s which will satisfy the join conditions with a given tuple tr , is actually a range on s . As an example , take into account student x takes . Suppose that we've a student tuple with ID " 00128 " . Then , the relevant tuples in takes are those who satisfy the choice " ID = 00128 " .

The price of an indexed nested - loop join is computed as follows : For every tuple within the outer relation r , a operation is performed on the index for s , and also the relevant tuples are retrieved . Within the worst case , there's house within the buffer for only one block of r and one block of the index .

Then , b , I / O operations are required to scan relation r , wherever br denotes the quantity of blocks containing records of r ; each I / O needs a seek and a block transfer , since the disk head might have affected in between every I / O . For every tuple in r , we have a tendency to perform an index operation on s .

Then , the time price of the join is computed as br ( tr + ts ) + nr * C , where nr , is the the range of records in relation r , and c is the the price of one choice on s using the join condition . We've seen before a way to estimate the price of one single algorithm ( presumably using indices ) ; that estimate provides us the worth of c .

The price formula indicates that , if indices are obtainable on each relations and s , it's usually best to use the one with fewer tuples as the outer relation . As an example , take into account an indexed nested - loop join of student x takes , with student as the outer relation . Suppose conjointly that takes encompasses a primary B + -tree index on the join attribute ID , that contains twenty entries on the average in every index node .

Since takes has 10,000 tuples , the peak of the tree is four , and an extra access is required to search out the particular information . Since n student is 5000 , the overall price is 100 + 5000 * 5 = 25,100 disk accesses , every of which needs a seek and a block transfer .

In distinction , as we saw before , 40,100 block transfers and two hundred seeks were required for a block nested loop join. Although the number of block transfers has been reduced, the seek cost has actually been increased , increasing the overall price since a seek is significantly a lot of expensive than a block transfer. However, if we had a selection on the student relation that reduces the quantity of rows considerably , indexed nested - loop join may well be considerably quicker than block nested - loop join.

#### Merge Join

The merge-join algorithm ( also called the sort-merge-join ) can be used to compute natural joins and equi-joins. Let r(R) and s(S) be a relation whose natural join is to be computed, and let R ⋂ S denote their common attributes.

Suppose that both relations are sorted on the attributes R ⋂ S. Then, their join can be computed by a process much like the merge stage in the merge-sort algorithm.

### Cost Analysis:

 Cost Analysis

Once the relations area in sorted order , tuples with identical price on the join attributes are in consecutive order . Thereby , every tuple within the sorted order must be read just once , and , as a result , every block is additionally read just once .

Since it makes solely a single pass through both files ( assumptive all sets S , slot in memory ) the merge - join methodology is economical ; the amount of block transfers is adequate to the total of the amount of blocks in each files , b + bs .

Assuming that bb buffer blocks area allotted to every relation , the amount of disk seeks needed would be [ br / bb ] + [ bs / bb ] disk seeks . Since seeks are rather more expensive than information transfer , it is smart to allocate multiple buffer blocks to every relation , provided further memory is out there .

For instance , with tr = 0.1 milliseconds per 4 - kilobyte block , and ts = 4 milliseconds , the buffer size is 400 blocks ( or 1.6 megabytes ) , that the time interval would be 4 milliseconds for each 40 milliseconds of transfer time , in alternative words , time interval would be simply 10 percent of the transfer time.

If either of the input relations r and s isn't sorted on the join attributes, they need to be sorted initially; the value of sorting must then be added to the above prices . If some some sets Ss , don't slot in memory , the value would increase slightly .

Suppose the merge - join scheme is applied to our example of student x takes . The join attribute here is ID . Suppose that the relations are already sorted on the join attribute ID . During this case , the merge join takes a complete of 400 + 100 = 500 block transfers .

If we assume that within the worst case only 1 buffer block is allotted to every input relation ( that's , bb = 1 ) , a complete of 400 +100 = 500 seeks would even be needed ; in point of fact bb is set abundant higher since we'd like to buffer blocks for under 2 relations , and also the seek value would be considerably less .

Suppose the relations don't seem to be sorted , and also the memory size is the worst case , solely 3 blocks . The value is as follows :

1. Using the formulae that we developed earlier, we will see that sorting relation takes needs [ log3–1 ( 400/3 ) ] = 8 merge passes . Sorting of relation takes then takes 400 * ( 2 [ log(3-1) ( 400/3 ) ] + 1 ) , or 6800 , block transfers , with 400 a lot of transfers to put in writing out the result .

The amount of seeks needed is 2 * [400/3] + 400 * ( 2 * 8 - 1 ) or 6268 seeks for sorting , and 400 seeks for writing the output , for a complete of 6668 seeks , since only 1 buffer block is obtainable for every run .

2. Similarly , sorting relation student takes [ log3-1 ( 100/3 ) ] = 6 merge passes and 100 * ( 2[log3-1 ( 100/3 ) ] + 1 ) , or 1300 , block transfers , with 100 a lot of transfers to put in writing it out . The amount of seeks needed for sorting student is 2 * [100 / 3] + 100 * ( 2 * 6-1 ) = 1164 , and 100 seeks are  needed for writing the output , for a complete of 1264 seeks .

3. Finally , merging the two relations takes 400+ 100 = 500 block transfers and 500 seeks .

Thus , the overall value is 9100 block transfers and 8932 seeks if the relations don't seem to be sorted, and also the memory size is simply three blocks .

With a memory size of 25 blocks , and also the relations not sorted , the price of sorting followed by merge join would be as follows :

1. Sorting the relation takes can be done with only one merge step , and takes a complete of simply 400 * ( 2 [log24 ( 400/25 ) ] + 1 ) = 1200 block transfers . Similarly , sorting student takes 300 block transfers . Writing the sorted output to disk needs 400 + 100 = 500 block transfers , and also the merge step needs 500 block transfers to read the info back . Adding up these prices provides a complete value of 2500 block transfers .

2. If we tend to assume that only one buffer block is allotted for every run , the quantity of seeks needed during this case is 2 * [ 400 / 257 + 400 + 400 =  832 seeks for sorting takes and writing the sorted output to disk , and equally 2 * [ 100/257 +100+ 100 ] = 208 for student , and 400 + 100 seeks for reading the sorted knowledge within the merge - be a part of step . Adding up these prices provides a complete value of 1640 seeks .

The number of seeks are often considerably reduced by setting aside additional buffer blocks for every run . As an example , if 5 buffer blocks are allotted for every run and for the output from merging the 4 runs of student , the value is reduced to 2 * [ 1100/251 +1100/5 ] + [ 100/51 ] = 48 seeks , from 208 seeks .

If the merge - join step sets aside twelve blocks every for buffering takes and student , the amount of seeks for the merge - join step goes right down to 1400/121 +1100/127 = 43 , from 500. the entire variety of seeks is then 251 .

Thus , the entire price is 2500 block transfers and 251 seeks if the relations don't seem to be sorted , and therefore the memory size is 25 blocks .

Check out my other posts related to DBMS:

Serializability

Remote Backup System

Aries Recovery Algorithm

Buffer Management

If you enjoyed and got some knowledge through this post, please share it with your friends and family members and if you don't want to miss my articles, you can subscribe us by sharing your email id through FeedBurner(click on the subscribe button in the right ). Comment below if you got any query related to this article.

Previous
Next Post »