 So, let us focus on the join operation, there are actually many different ways of doing join and I have listed 5 of these which are commonly used, there is something called nested loops join, block nested loops, index nested loops, merge join and hash join and each of these has an associated cost and depending on various conditions any one of these might be the cheapest for a particular query. So, depending on the specific statistics we will choose one of these ways to do a particular join. So, nested loops join is a very very simple thing, if I want to join R and S on whatever condition it does not matter what the condition is, the nested loops join runs as follows. It takes each tuple T in T R in R, then for each tuple T S in S. So, it is a nested pair of loops. So, it matches every tuple in R with every tuple in S and for that pair of tuples it sees if they satisfy the join condition, if so add the pair of tuples and do whatever projection is required we will omit that here and put it in the output. So, that is the core idea of a nested loops, very simple also potentially very very very inefficient. If I had a million tuples in each of the relations how long do you think this will take a million times a million will run forever it will never finish. So, nested loops join is a operation of last resort if nothing else can be used. What is good about it is that it can be used with any join condition, but the bad thing is it is very very inefficient, but there are variants which are more efficient as we will see. Some terminology this first loop the outer loop relation is called the outer relation and the relation which is used in the inner loop is called the inner relation. So, I can even flip I can use R as the outer S as inner or I can use S as outer and R as inner. So, I have a choice and the cost will vary a little bit depending on that. Now, nested loops by itself is very inefficient, but there is an efficient variant called index nested loops. So, what is index nested loops do? I do not have a slide which with an algorithm, but I can explain it using the previous one here. The outer loop is identical in index nested loops the outer relation I go over each tuple. For the inner relation I do not do a file scan here what I am doing is really a file scan I am checking each record to see if it matches the outer tuple. Instead what I do is I use an index. So, let us say the join condition is of the form R dot A equal to S dot A I have a particular outer tuple right now. So, what I want is all S tuples with a matching A value how do I find? So, let me repeat it the join is on R and S the join attribute on for the S relation is an attribute A. So, for a particular outer tuple I have a value and I need to find exactly those S tuples with that particular A value the question is how can I do this efficiently one way is to scan over the whole relation which we have just seen a better way is if there is an index on S on the attribute A I can use that index to find exactly those records with the given A value. So, what I will do is if I may switch back to the white board. So, for each R for each T R in R what I will do is I will take that particular tuple let us say let us say R is A B and S is A C. So, I have a particular R tuple here and let us say the A value here is 25. So, now I am going to use this index on S dot A and traverse that index to see the S record with the value 25. So, the join here is R dot A equals S dot A. So, with the value 25 I traverse down I find S record or multiple S records with the value 25 I get each of these and that T R record with that matching T S record which I fetch from here will go to the output and I will do this with each matching S record for that one R record when I am done I will go to the next R record do the same process and so forth. So, I take the outer relation I search the index find matching tuples and then output the pairs. So, that is the basic very simple idea that is index nested loops and it is very widely used. The cost of index nested loops though you will notice for each outer tuple it is going to probe the index. Now, the index if S is a large relation the index will take at least a few Ios to find the S record and if the number of tuples in R is large then there are that many random Ios. So, index nested loops join makes a lot of sense if either S fits completely in memory. So, there is no random IO the index on S rather fits in memory I should say the S and the index on S fit in memory then there is no IO for accessing S then it is very efficient or if R is small. So, the number of tuples in R is small then we do not have to put too much effort into we do not have to search through the S index that many times. So, then also it is very efficient. So, if S is very large R is small and I probe the index on S to find only the few relevant S tuples for each R tuple and the number of R tuples is small index nested loops join is very good. So, all that is reflected in the cost analysis here I will skip the details. So, that was index nested loops. The next alternative which is also very widely used is what is called merge join the idea of merge join is to sort the relations and then do a merge phase. So, what is going on here let us say there is a relation R attributes A 1 A 2 S with attributes A 1 and A 3 and I want to join them on R dot A 1 equal to S dot A 1. So, the first step is to sort R on A 1 to sort S on A 1 also both are sorted now on the join attribute the other attributes are not relevant we only need to sort it on the join attributes. Now, I have a pointer into each of those two relations P R and P S indicates the pointer into R and S respectively. Now, let us take the case where both of them match A and A. Now, I can output a matching pair then I can move forward if we assume there are no duplicates I can move both forward. So, I am now at B and B they match I output I move both forward what is the next pair I get I get D and C they do not match. So, what should I do I will have to move the one which is smaller. So, the C 1 pointer will move further and the next value there is D. So, what do I have now both are on D. So, at this point I have a pair of matching records I can output it, but here I cannot move both the pointers ahead because there is a duplicate there are two records with this with the value D on the left hand side the relation R has two records with D. So, actually I should not move ahead on S I have to match it with both the records on R in general both of them may have duplicates there may be multiple S records with the same value D and multiple R records with the same value D I have to match every pair. So, an easy way is to collect all these records and then do a Cartesian product of just these records and output every pair because all of them match with each of the others then move on to the next entry which will be m and f. Now, f is smaller. So, I will m at f and m here. So, I will move forward and get m and m match output move forward on this one I fall off the end that is it I do not even have to look at q I am done. So, that is it for the merge join algorithm very simple and very efficient if you know if sorting is efficient the step after sorting the merging is very efficient it is even faster that is why merge join is used quite often there are again details of the exact cost I am going to skip those and move ahead to hash join. Now, it turns out that merge join is very very efficient if the relations are sorted, but if you have to sort them in the first place it can be a bit slow and alternative is what is called hash join. I will show it by example. So, here is one relation R here is the other relation S and these are very big they do not fit in memory how do I join them using hashing the idea is this follows I will take the join attributes of R and the join attributes of S which have to match and I will compute a hash value which maps the join attribute to a range 1 through n for both of them. So, same function on applied on the join attributes of R and on the join attributes of S in this case that function maps to the value 0 through 4. So, each record I am going to apply the hash function and it goes to one of these 5 buckets the first record went to bucket 1 the second to bucket 0 the third to 3 and so forth the next 2 went to bucket 2 and so forth I will do that. And what I have done at the end is I have partition R into a number of parts. So, one pass on R outputting into 5 different files 5 different partitions of R I will do the same thing on S I will partition it also on the join attributes of S into 5 files. So, what do I have now each of these has 5 files. Now, the magic number 5 here was chosen carefully such that each of these files at least the S in this case is the smaller relation it is chosen such that each of these S i's is most probably going to fit in memory there is some randomness because of hashing, but I am going to choose the number of buckets such that S 0, S 1, S 2 this is S 0, S 1, S 2, S 3 and S 4 each of those is most probably going to fit in memory. Once I have done partitioning into so many buckets what is the trick I will read S into memory build a index in memory on S. Now, if you remember I told you index nested loops join is very efficient if one of the relations fits in memory. Now, if I take this case S 0 fits in memory and the key observation with hash join is the tuples in S 0 can only match tuples in R 0 why let us say that there is a tuple in S 0 let us take a join attribute values the hash function map that value join attribute value to 0. If there is any tuple in R with exactly the same join attribute value that is the condition for matching the hash function would match that R triple also to 0. So, it will be in R 0. So, there is no need to match S 0 with R 1, R 2, R 3, R 4 they will not match I only have to match S 0 with R 0. So, what I will do is build an index on S 0 then I go through R 0 take each tuple in R 0 probe the S 0 index and output all matches this is very simple index nested loops join between R 0 and S 0 with S 0 index fitting completely in memory S 0 and its index fitting in memory and. So, it is very simple very fast operation actually and I will do this for each I I will finish up with S 0, R 0 then move on to S 1, R 1, S 2, R 2 and so on at the end I am done I have output the entire join. So, hashing is a very powerful operation because it breaks up a relation into pieces each of which can be joined with essentially an in memory index built on the fly. So, the details are in all the slides here the details of how to do the hashing how to go about each step of the algorithm, but I hope the intuition is clear if it is not please read this later on I will just mention a couple of pieces of terminology the outer relation sorry the relation on which we built an index is called the build index the smaller relation is usually chosen as the build index because the number of pieces into which we need to partition it to ensure each one fits in memory is less. Supposing I have 100 megabyte relation being joined with a 5 megabyte relation it makes sense to partition the use the 5 megabyte relation as the build relation with let us say 1 megabyte of memory I break it into 5 pieces each of these will fit in memory. Now, what is the size of each partition of R if I partition the other one in 100 megabyte relation into 5 pieces each is 20 megabytes that is a lot bigger than memory with 1 megabyte memory, but who cares because the way the index necessary loop join works I am going to load this 1 megabyte of data from S into memory build an index then I just read that 20 megabytes of the corresponding R. So, if it is S 0 20 megabytes of R 0 is read sequentially one at at one block at time for each record I probe the index output to match and go on. So, the number of IOs is very small and I just needed to do a 5 way partition. So, the small relation will always be chosen as a build input the other relation is called the probe input because after building an in memory index I will probe using the other relation. And the number of way pieces in which to build it I told you it is a 5 megabyte relation memory is 1 megabyte I will break it into 5 pieces actually that is dangerous because there will be some variation in the sizes of the different partitions. So, there is usually a fudge factor. So, let us say if memory is 1 megabyte and the relation is 5 megabytes I will break it into 6 or 7 pieces. So, each piece will be 5 by 6 or 5 by 7 of a megabyte. So, a little bit of random variation does not matter it will still fit. There are lot more details about hash joins in the book for this talk I am not going to get into it. There is also stuff on how to do complex joins with a condition conjunction and so on. I am going to skip it for lack of time, but let us just take a couple of quiz questions. The first question is if two relations R A B and S A C are given sorted on attribute A then the natural join of R and S can be computed fastest using options are nested loops index nested loops merge and hash. So, please press the S T button be ready you have about 45 seconds from now. So, in the next 45 seconds please press the choice do not wait for the timer to run out press it before the time at least 5 10 seconds before it runs out. Time is almost up please make sure you have entered your answer. So, while we see the results tabulated. Now, let us look at the cost of the various join algorithms nested loops join as I said is very inefficient. So, that is almost always a bad choice index nested loops over here for each couple of one of the relations R I may have to probe S. So, there could be a lot of random IO with index nested loops join also merge join on the other hand the relations are already sorted. So, what does merge join do? It simply has to scan down R and S with one pointer into each do a merge which does almost zero random IO and it is extremely fast. So, the correct choice here is merge join how about hash join hash join has an extra cost what it does is it first reads the relations partitions them and writes them out then it has to read the partitions each partition back and then do build the index probe and output that is a lot of extra steps. So, in this particular situation merge join is by far the fastest algorithm. So, let us see how the results are again many sites have not managed to upload results 109 responses only and the majority of you have chosen the right option which is merge join that is good. Let us move on to the next question if data stored on a solid state flash disk instead of a hard disk which of the following join methods will benefit the most nested loops index nested loops merge and hash same four options, but here is a hint the hint is the difference between solid state disk and a regular hard disk is that the cost of a random IO is much much lower on a solid state disk. If you take sequential IO there is not much difference a random IO is what is much faster. So, the question is which of these algorithms will get the most benefit another way of putting it is which one has the maximum amount of random IO operations which will get reduced by using a flash disk solid state disk that is the question. So, now the timer has been activated please send your response, but still what we have is a good sample even if not 100 percent of the answers come in it is still very useful to understand what are people thinking in general and it also helps you to understand where you are you know there are many places where I am going very fast and you may think that hey I got the wrong answer, but at least you feel better when you see that many others got the wrong answer too. So, it means that you know you are not so bad after all it is just that I am going very fast over the topics and it means that if you go back and read it you will understand it just fine. While the result comes up let me discuss the answer first of all nested loops join has very very little random IO it is not going to benefit from having a solid state disk and of course, it is very inefficient in the first place merge join after sorting it has basically no random IO sorting itself does not have much random IO either. So, merge join will not benefit much hash join does have some amount of random IO, but again it does not have all that much it partitions the relations the read is sequential and there are tricks to reduce the number of seeks even for the output. And then when you read the partitions in again you are reading a lot of data at one go. So, the number of seeks is not that large. So, it does benefit, but index nested loop join is the one which has the maximum seeks because for each outer relation probe you are scanning down the index and then fetching the data if this index were on disk every single tuple here has a number of random IO's here. So, if this random IO becomes faster by a factor of how many is it from few 5 milliseconds to 1 microsecond factor of 5000 index nested loop join is the one which is going to benefit the most. In fact, for databases which fit completely in memory it is well known that it is a good idea to build indices in memory. The indices do not have to be B plus trees they can even be hash indices, but you build the index in memory and then use index nested loop join always it is well known to be the best approach for data which is completely in memory and flash is very close to memory. Now, the last few operations we need sometimes to do duplicate elimination for select distinct it turns out we can easily use sorting to do duplicate elimination. What do we do? Sort the relation on the full set of attributes if there are duplicates they are going to be adjacent in the sort order. So, first sort then read the things and all duplicates will be adjacent. So, we can easily remember the last record we saw if the next record is the same throw it out till we get a different record output it and keep going. So, that is cheap projection well outputting a few attributes is trivial projection combined with duplicate elimination well we just saw how to do duplicate elimination. How about aggregation? Aggregation on a whole relation without group by is straight forward read the whole relation read the attribute values compute the aggregate on the fly some min max each of these can be done on the fly just add the value add the count average find the sum find the count divide at the end all of these are easy. The only trick is supposing we have a group by operation in addition now how do we do aggregation it turns out this can also be done very efficiently by sorting on the group by attributes. So, if I say find the sum of salve by department for instructor I will take the instructor relation sorted by department. Now, I will start with the first instructor keep reading instructors one after another and keep on aggregating adding to the sum or count updating min or max till the department changes the moment the department changes I know that I have just found the aggregate for the first department I will output the aggregate then the next set of records is for the next department I can compute the aggregate output it and then move on. So, it is actually very very easy to compute aggregates once I have done sorting. So, sorting helps save the day again how about set operations intersection union set difference trivial with sorting union if you do union all and do not remove duplicates that is trivial anyway just output one set of records followed by the other, but if you want to eliminate duplicates what you can do is sort this relation sort the other relation and then when I basically do a merge eliminating duplicates along the way intersection very similar sort both the relations. Now, go sequentially down if there is a matching pair output the pair if it is not matching it is not in the intersection it is just like the merge step of the sort merge join the merge join is almost the same as intersection with some very minor difference that the join is on the whole set of attributes set difference again it is very easy you know sort the two relations go down if record is here, but not in the other output it and then move down just like in the merge step of merge join. So, all of those are straightforward I am going to skip the details and kind of wrap up with the last operation outer join which if you remember retains tuples from one of the inputs if it is a left outer join the left input right outer join retains tuples from the right input even if they do not match and similarly full outer for both. So, the key inside here is if you to merge join it is very very easy to turn it into outer join what do we do we have these two pointers let us say it is a left outer join. So, with the left input this is my left for the left input I will see if there is a match on the other side if there is a match I output it if there is no match I will output the stubble padded with nulls and then continue on on the merge phase. So, it is just like merge join with the one extra thing that if a particular left record did not match anything on the right immediately output it padded with nulls. So, left outer join is very easy with merge join how about right outer join similar full outer join also very simple. Now, what about doing outer joins with other joins you can actually extend hash join nested loops join and various things to do outer join I am going to skip the details. So, we will stop here I am sure you have questions raise your flag let me start with I will accept it from Vidisha Vidisha you have a question go ahead please yes I can hear you please. Sir, I have the two questions the first question is that while creation of the B plus 3 while we are creating it in the bottom of manner not in the top down manner whereas in the other case of anerid res creation we are doing in the top down manner. Yeah, your observation that we are essentially building it see I said something about bottom up building of B plus 3 in one sense every insertion into a B plus 3 comes down to the leaf and then the split propagates upwards in this sense insertions actually go bottom up in a B plus 3, but the specific term bottom up build of a B plus 3 refers to a slightly different process where we first sort all the entries and then using the sorted entries we build the tree level by level it is not that we are inserting from the root there is no tree we first sort create the leaf level then create the next level and then the next level actually it is a little more complex than that, but you can think of it conceptually like this we build the tree level by level from the bottom up and if you are building an index on a large relation from scratch this is a very efficient way it is much more efficient than inserting one record at a time. So did that answer your question or if not please go ahead and ask further. Sir, I have the second question too that while writing command in the SQL create index on the attribute list which indexing technique is used did it create the B plus 3 if in a normal manner of the attributes are there and if audio on the video type of the attributes are there then which type of the indexing techniques are used. That is a good question the first part is by default what index is created if you just say create index on pretty much every database the default index type is B plus 3 so that is what will be created. The second question is what about the type of the attribute so you can build a B plus 3 on attributes which are ints, strings, reals and so on no system that I know of will allow you to build an index on an attribute which is an audio or video type. In other words if the type of the attribute is C lob or B lob which is only way you can create large objects all other attributes have to be at most 1 or 2 kilobytes. So, on any large attributes the system will not allow you to build an index by now what do you mean when you say you want to build an index on a video or an audio that itself is not clear there are special purpose tools which will do special things so if you want to be able to seek to a position in an audio or break up a video image into frames and say go to the nth frame or nth short in a sequence so all of these are special purpose things which the database does not do anything about. So, you have tools built externally which can do all of this does that answer your question? Let us see who else has questions. Baramati has the flag up you are on if you have a question please go ahead Baramati. Hello sir. Yes. In hash join you are you have said it that key while doing the partitions of a table you are using a different function why you are using a different function why you are doing a partition and inserting a data into the table. So, let me phrase that question again the question as I understand it is in the book we explain that when we partition the relations we use one hash function and that same hash function is used for partitioning both the relations otherwise hash join will not work. The second part is after partitioning the relation we build an in memory hash index on the build relation the smaller relation typically and the point that we were making was when you build this hash index you have to use a different hash function because the first hash function maps the values to the range 0 to some n and everything in this partition will have the same value for that hash function. So, that is at a high level picture practically in terms of coding what is done is we compute some hash function which maps these values into a large range say all possible integers is the range. Then in the first phase we simply take that value modulo n if you are creating 5 partitions we take modulo 5 and that matches to one of these values. Now, the hash index which we build in memory we will again compute modulo something, but not modulo anything which is a multiple of 5 that causes trouble. So, we will pick some other number usually you pick some number of buckets which prime numbers tend to do well. So, you pick some number of buckets and take the same thing modulo that number to map it to one of the entries in the in memory hash index. So, that is what we mean by different hash functions. So, the implementation level you may use some hashing function and then take modulo 2 different values to get in effect 2 different hash functions. Does that answer your query? Back to you. Okay, thank you sir. Thank you. Let me answer a few of these chat queries. So, the first one is we have different types of packages for databases available. Can we find out what kind of storage strategy did they follow for storing the tuples? So, many of these products have manuals of internals where they have some description, but it is hard to find these details. It is hard to find out what exactly does PostgreSQL do and so on. You can do it. You have to dig through a variety of manuals. In some cases, you have to read the code of PostgreSQL because it is not very well documented. But a high level picture you can get for example, in our book, we have an appendix on four of the widely used databases and each of the appendices provides a little bit of internals information which has some of this stuff. The next question is what is the default indexing technique in Oracle? Is there any command to find default indexing techniques? All the manuals will tell you what kind of indexing will be used by default. Some of them may say you have to specify the type explicitly in which case there is no default, but the system manuals will tell you about this. They are all fairly clear about this. I am pretty sure Oracle uses B plus trees. I am pretty sure everybody uses B plus trees by default. The next one is can the records be arranged randomly in a sparse index? No. You can have a sparse index only if the records are sorted on the search key. Otherwise, it has to be dense. If you are not sure, read it up. It is there in the book. The next one is if you want to integrate our indexing technique, a new indexing technique with any free source database, how do we do it? That is actually a good question. If you come up with a new indexing technique, how do you integrate it with a database? Now, PostgreSQL is a database which was designed to be extensible. One of the features they had is the ability to add in new index types. There was a new index type called GAST, which was added to PostgreSQL. There were new index types, so keywords indices which were added to PostgreSQL. From my experience with it, these are not easy. You really have to spend a lot of time reading the documentation and code to understand how to add a new index type. So, it is not a trivial project. If you have an enthusiastic student, it could be a B project kind of thing. It is probably way too much for a typical course project for undergraduates. Although for master's ME students, this could be a course project. Next one is in fall values are stored at leaf node. Then, for separate search of values, do we require the same number of access? I am not sure what this means. Maybe, this is related to B trees as supposed to B plus trees. In my lecture, I completely skipped this issue of B trees versus B plus trees. The original description of B trees allowed pointers to records from internal nodes and they were called B trees. Soon after that, people said that it does not actually help to store pointers to records in internal nodes. Let us have pointers to records only at leaf nodes and this structure was called a B plus tree. Now, eventually what happened is nobody really implements the original B tree anymore. Every implementation today is what we call B plus tree. However, in the industry, people do not bother to add the plus. They just say B tree and by default, they mean what we call the B plus tree. So, if you read somewhere that is a B tree, 99 percent chance it is what we are calling B plus tree, not the original definition of a B tree. Next one, in implementation, I found deletion is very difficult. Can you give simple algorithm for deletion? We have given pseudocode for deletion also, but if you want to implement, there are a few details which you have to deal with. You can find the code on the web which implements B plus tree insertion deletion or if you want, send me mail. I can dig up some code which we have somewhere. Last few questions, last question unrelated to indexing, while inputting any string which has apostrophe in a web application under development, it generates an error terminating the data there only. Well, I have been saying repeatedly that you should be using prepared statements properly. If you see an error with a quote, that means you have just written an application which not only crashes with quotes, but is vulnerable to SQL injection. So, please go back and read the stuff on chapter 5 JDBC on SQL injection and how to avoid it. So, that should solve your problem. And if you see this error occurring, you are leaving a door wide open for people to come in and destroy your application using SQL injection. Bye.