 Hello friends. So, in the previous lecture we have seen nested loop join and block nested loop join. Today we are going to learn index nested loop join. So, learning outcome for this session is students will be able to apply the index loop nested loop join and analyze its cost. So, let us recall what this nested loop join. So, we have seen that if we want to want to find the result of r join s where r and s are two relations then for every tuple of r which is an outer relation we are going to check the tuple with s. So, for each tuple of r entire s will be scanned and the joining the pairs which are matching on the joining condition theta that will be obtained. Then we will go for the second and so on. This is nested loop join and the number of block transfers and seek operations are very high in nested loop join. That is why we have gone for block nested loop join and now today we will see if the indexes are available for the inner relation then we can go for index nested loop join. So, here what is index nested loop join? So, in index nested loop join if index is available or in nested loop join if index is available in inner loop joins attribute then we are going to replace this file scan by index lookups. So, here the attribute the tuple with that particular value or the joining attribute will be matched or will be found using index. So, for each tuple tr in outer relation r the index is used to look up the tuples that will tuples in s that will satisfy the join condition with tuple of r. So, it is as good as using that indices. So, the existing indices can also be used or temporarily indices can also be created for evaluating join operation. So, here for each tuple in outer relation r a lookup is performed on indexes and irrelevant tuples are retrieved. So, finding the index is nothing but what we are doing actually is that looking the tuples in s which will satisfy the condition on joining attribute that we are going to see. So, we are going to see this example once again. So, we are having two relations one is student and other one is text. So, in students the attributes are id name and department name where in text it is id cos id section id semester year and grade. The number of records in student relation are 5000 whereas blocks assume to be 100 and the number of records in text relation are 10000 and the number of blocks are 400. So, let us look at this suppose this is the sample data. So, student is having 5000 records and this text is having 10000 records which is again id is the common attribute in between student and text. So, when we are trying to match it in nested loop join what we does is that we take the first relation or first tuple and then try to match it against all the records of s. But here instead in index nested loop join we will be taking the first record or here I have taken an example of the tuple 3. So, for example, third record has been taken. So, what is the id here 3? So, what we will do is that we will find all the records from this relation text having the condition that id equal to 3 index. So, here it is an example. Now, when we are doing any operation or the query processing it may be the possibility that the entire relation fitted in main memory or if it is not fitting then we have to bring the blocks that fits in main memory. So, assume that we have taken the first block of outer relation are here then how it will be matched to s. So, in contrast to nested loop join it will not be matched with every tuple of s, but it will be matched as per only the indices. So, this is an example. So, first block has been taken. Now, the first tuple will be taken that is called as in tr for example. So, first id is here 1. So, it will be checked in the index of text relation. So, again this relation or the index is available in main memory. So, lookup will be more faster. So, assume that there is a primary index available on id in text relation. So, what it will does that tr tuple. So, id will be taken since it is in joining attribute. If you are having more than one attribute then we have to check the whether the index is available on that attributes. So, here one will be taken in index it will be searched. So, this is the first record itself. So, it will take all the record sequentially from this particular data. So, only first and second are matching. So, that will be written or that will be added to the result. Then again second record will be taken from tr index will be look or there will be the lookup on index. So, second it is pointing to again suppose two records then it will be retrieved the matching records and then the data will be added to the results or tr or tuple concatenation will be added to the result. Then the third record and so on. So, till the last record this will be matched and the data will be added to the join operation. So, here what is the difference is that in nested loop join when we have taken the first record it was checked against all the records of S. Here it is replaced only with the indexes. So, that it will be checked only with the records that are available for that particular id or for that particular index. This is how it will save the comparisons, but yes it should the index should be available and that should fit it in main memory then only it will be easier or it will be more faster. So, this of course the result of join operation is not going to be changed, but only the thing is that how it is work using index nested loop join. So, now let us analyze the cost. As I told you if the both the relations are fitting it in main memory that is the outer relation and the index of the inner relation then we will not be requiring much cost to have the block transfer. So, there will be only one read of that block transfer and sick for every relation, but in worst case assume that there is a space in the buffer only for one block of R. So, here we have seen that that is only one block is fitting in the main memory. So, how many blocks we have to bring here? As many blocks of the outer relation. So, in our example there are 100 blocks. So, if only one block is of R is fitting and one block of index is fitting then we will be requiring BR IO operations because we are having that many blocks. So, BR is nothing but the number of blocks containing the records of R. So, BR IO operations are needed to read the entire relation R and each this IO operation requires one sick and one block transfer. Since disk head will move in between that is why we have to do one sick and one block transfer. So, that is why and now there are R tuples in that relation. So, the one block will be brought to the main memory then for every tuple in that block we are going to perform the lookup on S. So, if you remember now this block has been brought to main memory and then one by one record will be checked in the index available over there. So, here for each tuple in R perform index lookup on S. So, what is the cost of join operation? So, the number of blocks that we have to bring to the main memory is BR. So, that is BR plus how many times we are checking as many records are there in the outer relation. So, NR is the number of records present in relation R and C is the cost of single selection on S using joint condition that is why the total cost will be BR plus NR into C. So, if the indices are available for both the relation it is generally most efficient to use one with fewer tuples as the outer relation because if we can see here the cost is going to be number of tuples into C. So, here 5000 into C, but if we take text as an outer relation then it will be 10000 into C. So, this is the example now. So, assume that text is a relation which is the inner relation and is having the primary B plus tree index on join attribute ID. Now, it assumes that it takes 20 entries on an average in each index node. Now, we are not going in the detail of B plus tree. So, you can watch the other videos for B plus tree. So, text is having 10000 tuples. So, height of the tree is 4. So, one more access will be needed to find the actual data. It means that we have to reach to that particular index and then from that index we have to read the data. So, that is why we are the number of what you can say that the operations required are 5 or the cost of lookup is 5. Number of students in number of records in student relation is 5000 and the blocks are 100. So, total disk accesses will be 100 plus 5000 into 5 which is nothing but 25100. So, each of which requires a one seek and one block transfer. So, if you will compare this 25100 block transfers which are very less as compared to block nested loop join where we have required 40100 block transfers plus 200 seek operations. But we have already just seen that every operation requires one block transfer and one seek. So, here 25606 will be required whose cost will be very high as compared to block nested loop join. So, I hope you have understood how to use index nested loop join and how to find its cost. Thank you.