 So, let us move on to query optimization. This chapter actually has a lot of material, but I am going to not cover most of it. Even when I teach my course here, I tend not to have too much time for query optimization and I am not able to cover the whole chapter, but in this short course I am afraid I have to make it even more brief. So, the key point of query optimization is that there are many different ways of evaluating a query. You might specify the query in some way in SQL. There are many different ways in SQL. And even if there is only one way in SQL, that one way in SQL may have many different ways of being expressed in relational algebra. And the many different ways in relational algebra can be implemented using many different algorithms, merge join, hash join, index nested loop join, which leads to a very large number of alternative plans, which will all give you the same result. You could use any one of them and how do you choose between them? Well, you want to choose the lowest cost plan amongst all of those. And how do you find the cost? You have to have cost destination. So, that is what the query optimizer is all about. So, first let us start with this plan. It is doing natural join of instructor teacher's course and then select department name equal to music and project on name title. This particular query is actually, you know, does not do exactly what you might think it does, because course dot department is equated to instructor dot department. We had discussed this query as a pitfall for natural join. So, somehow this slide came with buggy version of the particular plan, which has that bug, but it is ok. Let us pretend that we are only interested in instructors who belong to the same department as the course, then this query is correct with that formulation. So, anyway coming back, what we had was a select on top of this join. If we compute this whole join, we are joining the entire teacher's relation with the course relation. These are pretty big and then join that with the instructor relation, which is also pretty big. And then we are restricting it to department equal to music. So, what we have done is, we have computed information for all departments and then we throw everything out except music. It is probably not a very good plan. Now, here is an alternative plan. I pushed a selection on department name equal to music down on instructor. And so, I am only going to consider instructors in the music department. How about on this side, I am going to do the join of all of teachers and course and then join that. So, this is a better plan because it at least reduces the number of instructors that I am joining, but you can do better. I could have even pushed the department name selection, which will apply to both course and instructor. I could have pushed it down on course also and reduce this further. So, this business of taking query like this with some selection and transforming it to an alternative form. In this case the selection got pushed down. There are many other transformations possible, which guarantee the same result, but the cost may be widely different. So, here is another version of this, which is the same as the second plan before. But, early in the last plan, we just had the operator without saying how they are evaluated. This is called a logical query plan. Logical in the sense, we are not spinning down the physical details. Logically, this is what we want to do. This on the other hand is an evaluation plan and this spins down the details of how we are doing each of these steps. So, in this case, we had pushed the selection down an instructor and we decided to do a merge join here. So, we had a sort operator here and here to sort the inputs. Now, teachers join course, again we had to join it. So, we chose hash join in that case and we also had pushed a projection on course, on course ID title. This by the way, so this is not the same query. It looks similar, but this is the corrected version of the query, where we eliminated the department name here. We did not want it. So, this version actually computes the full, you know, instructors who teach courses in other departments are also considered here. So, this project has been pushed down. So, we have annotated this whole thing with saying, tuples are pipelined from the project to this hash join and from this sort, they are pipelined into merge join. From this sort, they are pipelined into merge join. Now, sort itself is really two operator, which is the run generation and a merge. So, really you should think of this as two parts. Over here, we use index one, that is the name of the index to find instructors in the music department and we are pipelining those results to the sort, actually to the run generation part of sort. As the run generation will complete and only then pass tuples on to the merge part of merge phase of sort. The merge phase output is pipelined straight to the merge join. Similarly, here the merge phase output is pipelined also to the same merge join. Now, this project in turn does the sort to remove duplicates. Here, the sorting was an ID. That is not useful for name title. So, we will need one more sort to do that. I have not shown the details. So, that is the complete query evaluation plan. There are several things here, but before we get into it for today afternoon lab, you would be looking at query plan. So, as I told you earlier, you just prefix the query with explain to see what plan is chosen. How exactly to do this depends on the database you are using. For example, in SQL server, you can say set show plan underscore text on. After this any query which is submitted will not execute the query, instead it will show the plan. Then, set show plan takes off will stop that behavior. Other oracle has its own syntax for explain and so forth. So, now let us see how a query optimizer works. So, the steps are shown here. The steps in cost based query optimization. Now, many systems earlier did not do full cost based optimizer. They used what are called heuristic optimizer. What that means is they did not necessarily have a cost model. They would have heuristics which say that in general, if you can do a selection before you do the join, do it first. Do not postpone the selection to after the join. And some of these heuristic generally work, but there are other things which may or may not reduce the cost. And you use do that or not. Well, it is not clear that is where cost based optimization comes in. You need to estimate cost to make the choice. So, now cost based optimization works conceptually as follows. Generate logically equivalent expression using equivalence rule. And we are going to see these rules. Annotate them to get alternative query plans. And then choose the cheapest plan based on estimated cost. In reality, it does not quite work in this sequence because the number of plans is very large. So, you do not want to consider every possible plan. So, there are ways to avoid this and still get the best plan. And we will see it coming up. So, the plan cost estimation is based on a number of statistical values, number of tuples, number of distinct values, histograms and so forth. For input relations, these are pre-computed and stored. So, it is possible in possible to look at the statistics for a given relation. I have not provided that to you, but the information is available. How to look at the statistics relations in Postgres? To find out what all statistics it has about a particular relation. But it is not only the input relations for which we need statistics. If you have a three way join, the first join gives some results. I need its statistics, how many tuples it has, how many distinct values it has and so forth to estimate the cost of the next join. So, we need a way to estimate statistics for intermediate results. So, again, there are techniques for doing this. Due to lack of time, I would not be able to cover this part, how to do the statistics for intermediate expression. Again, it is there in this chapter 13 of the book and in the slides, in the detailed slides. So, I will leave it out from here. I am just going to look at the basic intuition of query optimization. Once you have the statistics, the cost formulae which we saw earlier can be used. In the cost formulae, we used for a little simplistic. Real optimizers use more detailed statistics and more precise cost estimation, including CPU cost and so forth. Let us look at the first thing, generating equivalent expressions and just summarize what it said. You do not need to read it. So, basically two expressions are equivalent if they generate the same multi set of tuples. For SQL, it is a multi set. So, in SQL, the number of duplicates matters. So, if there are two expressions which generate different numbers of duplicates, then they are not equivalent. So, that is what we are going to focus on and all the equivalence rules here work with the multi set version also. So, let us look at some of the equivalence rules. The first equivalence rule says that select theta 1 and theta 2 and E is the same as first applying one of the conditions say theta 2. On that result, applying the second condition theta 1. I think this should be evident that if you have a condition which is a conjunction A and B, you can check A and then you can check B or you can check B and then you can check A. So, that is straightforward and you can flip it. So, that is the commutativity. Theta 1 theta 2 in this order can be flipped to theta 2 theta 1 that is this other order. So, this one first applies theta 1 then theta 2. This one first applies theta 2 then theta 1. Note that all these thetas are specific selection conditions like r dot A equal to s dot B is a joint condition, but that could also be apply here r dot A equal to 5 is a simple selection condition that could be here. The next rule is very simple. We have a sequence of projection operation which remove attributes one after another. I can collapse them into one single thing which is the last one. Assuming this syntactically correct l 2 must be a superset of l 1 and so forth. So, I can directly project on l 1. This is kind of trivial. We will skip it. The next two are very useful rules. The first rule says that if I have a select on top of a cross product that is equivalent to a joint, a theta joint. Now, why is this important? If you look at the SQL syntax of the simplest syntax in SQL, you will say select something from r 1 comma r 2 where some condition that r 1 comma r 2 is turned into a cross product. Now, if I actually compute the cross product it could be very very large, but if I push the where clause can join condition in it turns into a joint condition and now I can do this more efficiently. So, this transformation is very important for a SQL query. The next is a variant of that. It says if there is already a condition and there is one more joint condition here, I can push it in here and do a join with two conditions theta 1 and theta 2. So, the select condition here was pushed in here. So, this can be also very useful. The next set of rules is about the equivalence of different joint order. Now, when we wrote the SQL query we just listed them r 1, r 2, r 3. We did not care about in what order we wrote them. So, implicitly we are saying that the order in which we write these things is irrelevant. That intuition corresponds to these set of equivalence rules based on there are two rules commutativity and associativity. The first rule says that if I have an expression E 1 join with an expression E 2. I can flip it E 2 join with E 1 is really the same thing. By the way I should stress on this I am using the notation E 1, E 2, E 3 here. By that I mean E 1, E 2, E 3 can be any expression. Could be a complex expression. I do not care. It could be a relation. That is okay. But this equivalence rule holds regardless of what this expression is and what this expression is. This equivalence will hold. If I flip it the result will still be the same. Well technically the order of attributes will get changed. You have to deal with that. So some reordering of attributes may have to be done. But we will ignore those details here. So that is commutativity. Associativity says basically this. This is again for natural join. Then the theta join version of it. Slightly more complex. Natural join associativity says the following. If I take E 1 join E 2 and then join it with E 3. That is really the same as first joining E 2, E 3 and then joining with E 1. So doing this two first and then this one is the same as doing these two first and then this one. So let me show a picture for this to make stress this again. That rule which we just saw says E 1 join E 2 then join with E 3 is the same as first joining E 2, E 3 and then joining with E 1. So that is join associativity. A natural join associativity. So commutativity. This is commutativity. E 1 E 2 turn into E 2 E 1. It turns out that commutativity plus associativity. This pair is equivalent is enough to show that the join order is for inner joints is irrelevant. Now it turns out that associativity does not work for outer join and nor does commutativity. So the join order is actually important for outer joints. You cannot say it in any order you want. But for regular inner joints the join order is irrelevant because commutativity and associativity fold. This last part says how do you extend associativity for theta joints with condition. Essentially we have to decide where to place each condition. For lack of time I am going to skip that detail. The basic intuition. I will give you the intuition. The intuition is that when I join E 2, E 3 I can only apply those parts of this condition that involve attributes from E 2 and E 3. Then when I join E 1 with this thing I can apply all the remaining conditions. So here what happened is I had a join condition between E 1 join E 2 with E 3. Some of these joint conditions involve E 2. Some of them involve E 1. So those which involve E 2 can be done here. Those which involve E 1 namely theta 3 have to be done later here in the final joint. That is the only minor difference. This next rule is also very very important for query optimization. What this rule says is if I have a selection on top of a joint where the selection condition theta 0 involves only the attributes of one of the expression. Let us say E 1 being joined. What this equivalence rule says is if the selection were above I can push it down into E 1 and do the selection on E 1 before doing the joint. This is very important. If you go back to the query we saw some time back here I had done the selection later on. But that means that I am generating results for many other departments which I do not care about. What I did is I pushed the selection on to instructor. So I restrict instructor to only instructors from department named equal to music. So that is the effect of this particular rule which is called pushing selections through joint. There is a small variant of the rule which is that the selection has two conditions which are ended together. One of them is on E 1. One of them is on E 2. Then I can push part of it into E 1 and part of it to E 2. So these are the most important ones commutativity, associativity, pushing selections. The others are in some sense secondary. If you do this you can handle many, many queries. But of course there are other queries which need more rules. So first of all in the book we have more things on pushing projections through joint, equivalence, associativity for set union intersection, commutativity also for these. Set difference is little more restricted and then some other rules. And finally there are a bunch of rules on aggregate. So when you can push selections through aggregates and so forth. Again some of those are in book exercises and then there are other many others which we did not have space to cover. So bottom line is there are many equivalence rules which we can use to generate equivalent expressions. So what you might do is take an expression and apply multiple rules to get a new expression. So this one shows the result after pushing the selection on music, department equal to music into instructor and year equal to 2009 into teachers. So first it got pushed here and this got pushed here. Furthermore if you see the join order is different. Here teachers join course is first. On this side instructor join teachers is first. So we have also done join associativity. So we have done many things and this is a more efficient thing because we are only looking at music teachers with courses in 2009, courses taught in 2009 and then looking joining with courses. But of course the big question is in what order do we do this and how many alternatives do we generate. If we naively apply these transformation rules it blows up in our it is a huge number of possible expressions you could create. It is enormous you know it is like even with 10 relations you are looking at tens of millions of alternatives it is huge. So we really cannot generate all the alternatives. There has to be a better way and we will briefly see some of the better ways. So I think in the interest of time I will not actually run this quiz but I will just explain the quiz question anyway. So the question is the expression sigma r dot a equal to 5 and r join is equivalent to which of these expressions given these two schemas. I will let you read this for a second and then I will answer the question. If you have read the question now let me explain it. The thing here is a select on top of a join. We can push the selection on to one of the relations. In this case the selection is on r dot a equal to 5. So the correct answer is the first one. We have done the selection on r dot a equal to 5 on r and then we join with s. So this is the correct answer. This one is doing r dot a equal to 5 on s which is syntactically meaningless and the other answers are not meaningful. So now let us come to the issue of join ordering. So if you have relations r 1, r 2, r 3, join associativity says that if I have this particular order it is equivalent to this particular order. But it is actually possible to get even more order. For example if I apply commutativity first here I will flip these two. I will get r 2 join r 1. Now if I apply associativity I get r 2 join with r 1 join r 3. That is yet another plan. So there are many such plans. It turns out that the sizes of these things could vary wildly and the costs correspondingly could also vary wildly. So for example it may be that r 2 join r 3 is very large but r 1 join r 2 is small. So maybe r 1 is a very small relation r 2 and r 3 are very large. Why would r 1 be small? Maybe originally it is a small relation or maybe r 1 is really a select on a relation and the selection condition makes it very small. May be just one tuple. In which case it makes sense to first join r 1 with r 2 which might give us a small result and then join that small result with r 3 which also gives a small result. And maybe index necessary to join it was nicely here it would be a very efficient plan. Whereas r 2 and r 3 are big relation. Joining them first would take a lot of time and then almost all the results of the join are discarded in the next join. So this is another example which I will. So this shows the previous one which had a select department name equal to music on instructor joins teachers join course. So if we did teachers join course first we would come up with a lot of tuples because every teacher's tuple is matched with its corresponding course tuple that could be pretty large. Whereas very few teachers are in the music department but if we first do instructor in the music department that is a small result. And then I can join that with teachers that is a small result then join that with course that is also small result. So now what I have shown you is that there are many alternative plans which can have very different cost. How do you find the cheapest plan amongst all of these? So there are two broad approaches. One is the equivalence rules which we just saw can be used as is but if you do it in a naive way it would be very very expensive. So there are optimizers which have come up with a very nice way of applying these which can keep the cost relatively small. It is still not cheap it is still exponential cost exponential in terms of the number of relations and operations which are there in the query. It is not cheap but it is not as bad as if we actually use the rules naively and generate every possible plan. Remember we discussed this in the context of Armstrong's axiom. We said there are lots of axioms you know well there are three axioms Armstrong's axiom. You could apply them on set of functional dependencies and infer more and more and you infer a very large number. But we do not really want to infer all of them. Do I really want to generate all the plans? No my end goal is to find the cheapest plan. If there is a way to avoid generating some of the plans while I still guarantee I will generate the cheapest plan. That is what I should do. I do not want to see all the plans. I just want the cheapest among them. And there are many tricks to doing this which are all part of this. There is a optimizer which was called Volcano which was developed in around 1992 time frame and that optimizer showed how to use transformation rules and efficiently find the best plan. So, that was a huge step forward and subsequently the person who developed it good graph he went on and implemented it. It is now part of several commercial databases including Microsoft SQL server. The other approach is much older. It dates back to the very first relational database prototype called the system R algorithm. So, instead of doing transformation based optimization system R said I primarily care about join order. Well I also care about selections. So, I am going to handle selections heuristically by doing them as early as possible. In other words I will push selections down to the lowest level possible and then I will pick the join order after doing this. And for other operations such as aggregates they had some heuristics. We are not going to consider that, but this is actually a huge step forward. This was a landmark result because before this there was no efficient way of finding the best plan even considering just join orders and selection pushing. This was actually enough for most queries. Volcano came in when the need felt for much more complex queries. For all this simpler queries which were used in the earlier era this is more than enough. It performed wonderfully and this was a really a landmark paper. The paper which described the system R optimizer was key to the success of relational databases. So, of course that paper also had to talk of how to find the best join order and how to do cost estimation. Again I am going to skip details of cost estimation and focus on finding the best join order. So, it turns out the best join order there are many many join orders. So, how many join orders are there? There is a function to calculate it. I will not get into the details, but with n equal to 7 there are already close to a million order. With n equal to 10 it belongs to not millions, but billions 176 billion if you actually generate all of them. It turns out it is a complete waste of time to generate all of them. In fact we can do this in time proportional to 2 power n or 3 power n. What is 2 power n with n equal to 10? It is 1000. 3 power n I do not remember the exact number but it is not all that big. It is definitely significantly more than 1000, but it is quite tolerable. It is very practical. So, the idea is to use dynamic programming. So, what we do is if you take any subset R 1, R 2, R n the idea is that the best join order for this subset is going to be computed once and stored and it may be used many times when I am finding the best join order of some super set of this. So, let me show this intuitively and then I will give you a pseudo code for it. So, if I want to find the best join order for a set of n relations, first consider all ways to break this up into two sets S 1 and S minus S 1, where S 1 is any non-empty subset. There is also a bug in this slide. Any non-empty subset which is not equal to S, it is non-empty strict subset I should have said strict subset of S. So, I can break this up in different ways. Now, I will recursively compute the best order for S 1 and the best order for S 1, S minus S 1. What I mean by best order? Again I am going to depending on the join order, there is also issue of what is the best join algorithm. I am kind of skipping those details, but it is part of the pseudo code coming up. So, taking into account the best join algorithm, what is the best order for S 1 and for the remaining thing. Now, at this node for a set S, there are actually 2 power n minus 2 alternative. That is a lot of different alternative. So, I will recursively compute the cost for each alternative and find the cheapest here. But the key thing to note is that, if I am called with a particular set S 1, of course, or a particular set S here. I have to do a lot of work to recursively compute this, but the key thing to note is that, find best plan of S is going to be called many, many times. How many times is it going to be called? It is going to be called once per superset of S. For every superset of S, that superset will be broken into S and something else. So, it is going to be called potentially many, many times. How many supersets are there for a given set? It is exponential. So, it would have been called huge number of times, but the key to dynamic programming is to say, compute it once and if it is required in future, look it up. It does not have to be computed over and over again. That is the key idea. With that key idea in mind, let us show a recursive procedure for finding the best plan for a given set S. So, the first step is to see if best plan S dot cost has been computed already. It is going to be infinity if it is not been computed. That is the data structure I have. If it has been computed already, I just return it. I do not have to do any more work. If not, I have to do some work. First of all, if S is just one relation, let us straight forward. Find the set the best plan S dot plan and best plan S dot cost based on the best way of accessing S using selections on S and indices on S. So, I have some selection conditions. If an index is appropriate, I will use it. Otherwise, it is a file scan on that relation. Whichever is the cheapest, I will find its cost and set best plan S dot cost and best plan S dot plan. I will save that. Otherwise, if it has more than one relation, two or more relations, I will find for each non-empty subset S 1 of S such that S 1 not equal to S. This is same thing as before. Find best plan of S 1 recursively and recursively and find best plan of S minus S 1. Now, find the best algorithm for joining the results of P 1 and P 2. The cost is the P 1 dot cost plus P 2 dot cost. What are P 1 and P 2? These are plans, execution plans. So, I will add the cost of P 1, the cost of P 2 and the cost of the best algorithm for joining these. That is the cost of this way of breaking it up. Now, I am looking at many alternative ways of breaking up S into S 1 and S minus S 1. So, if the cost which I have just found is less than best plan S dot cost, initially that would be infinite. When I find one plan for it, that cost would be less than infinite. So, I will follow this step. I will set best plan S dot cost equal to this cost and the plan itself is execute P 1's plan, execute P 2's plan and then join the result. Well, actually it would not be text. It will be a tree which says join of left child P 1, right child P 2, left child is P 1 dot plan, right child is P 2 dot plan. I have just shown it like this textually. So, looping over all the alternative ways of breaking up S, there will be many alternative. So, each one I find the cost. This if condition keeps track of the minimum cost amongst all the alternative and at the end I have saved up the minimum cost, I can just return it. There is a technical detail to allow index nested loops join on plans that also have on relation that also have selection. If you are interested go read the book. So, that is it for join order optimization. There are many more details. It is not really true that that is it. It turns out the order in which the output is generated also matters a lot. I have skipped that detail and the book has details of the sort order. The sort order can have an impact on join algorithms later on. If the result is sorted on the join attribute, a join is super cheap. If not join may be more expensive. So, taking that into account the algorithm can be modified. Details are in the book. I think this is a good point to take a few questions. So, let us go back to live and take questions. Then I will come back and wrap up this chapter with a few other Kongu engineering college. I mean not please go ahead. Sir, how to scale a database? How to scale a database? First let me explain what it means to scale a database. So, supposing you have a business which has a certain number of customers today. As your business grows, you want your database to handle your increasing customers, increasing sales, increasing employees and so forth. If you are a website, scaling is even more important because when Facebook launched, it was used only in Harvard. So, it had a few hundred people, then a few thousand people. Then it spread to multiple universities, tens of thousands, hundreds of thousands. Then it went to millions, tens, hundreds of millions and now it is like around a billion. Now, in this period, the scale of operations has expanded tremendously. When Facebook initially launched, you could probably just run it on one small MySQL database running on one machine. Not a problem, but clearly today that is clearly absolutely impossible. It has to run on a large number of servers running on parallel. So, scaling the system means how do you design it such that if the needs grow, if more and more users join, we should be able to add more machines and handle the increased load by having more machines. Of course, we should also optimize the code such that it does not use resources recklessly. It is careful about using resources, but after this has been done the only way to scale is parallel. So, there are two parts. Generally, we focus on scaling an application and then there is another issue of scaling a parallel database. So, one way to handle it is to have a parallel database which can take many queries in parallel and that database itself should be scalable. We will talk a little bit about this in the context of big data, but there is a lot of interest in this area. Scalable data storage system, scalable databases where you can keep adding machines a few at a time and migrate some of the load on to the new machines and thereby keep growing as your needs grow, you can add more and more machines and grow with it. The key thing is you do not have to shut down the database in the midst of all this growth. There is a older generation of parallel databases which did not allow this kind. They were scalable, but they would require a brief shutdown when you added new machines. That is a complete no-no in today's web era, where you cannot bring down Facebook in order to add a few machines. Facebook has to keep running even as I add more and more machines. So, today's parallel systems used for such applications are scalable and they can be scaled dynamically without shutting down. Does that answer your question? Sir, I have another question. Is it possible to get the deleted tuples from the table after commit? The question is, after you run a transaction that delete tuples and it commits, can you get the deleted tuples? By and large the answer is no, but specific databases have some features which can help you undo mistakes. So, oracle for example, when you delete tuples from a relation, it gets stored in some other area. They do not actually delete it immediately. They will collect it later if the database is running out of space. Those deleted tuples will be garbage collected later on. But, if immediately after the delete you realize you made a mistake, it is possible to go back and get the deleted tuples. The way you access it is oracle specific, but not all databases support it. I do not think PostgreSQL supports that. Any other question? Thank you. Before we take another live question, there is a nice question on chat which says which type of index is appropriate to this situation. The situation is in board exams, assume that some 2 lakh records are there which is conservative. If you look at all the boards across India, it is much bigger. The number of people who take JEE is JEE main would I do not know the numbers this year, but it will be well over million. Several, it was a million few years ago. I do not know what it is now, but it must be much more. So, now when the result is announced, each student will try to search his or her result. What kind of index is effective? That is a good question. I will give a two part answer to this. First of all, any index which has to go to disk is going to be extremely slow when 2 lakh people are accessing the data. If you require a disk access per record here, you are dead. But the good news is 2 lakhs is not a large number. It may seem like a large number, but 2 lakh records with all their marks and so on. How many bytes do you need per record? 100 bytes? 2 lakhs is 200,000. 100 bytes is what? 20 million bytes. That is nothing. So, the first step is you keep everything in memory. That is the best thing to do. Do not even go to the database. So, you can completely avoid the overheads of going to a database by doing the following. First, read all the results into memory and build an in-memory hash index on that. You can do this in Java by just using a standard hash map. That is a built-in data structure in Java. Use it. Now, everything is in memory. So, when a candidate comes with a look up, looking up their roll number, you just look up the roll number, retrieve the data which you have already retrieved from the database and stored in memory, return it. This is by far the most efficient way, bypass SQL. This is exactly what we did for gate results. Gate also has something like million people taking the exam currently. We wanted to be able to support this load. We did this some years ago when the load was 300,000 or so. It was not a million. We just did this and we tested this. We showed that within one hour, we could handle the entire load of 300,000 people, all coming in one hour. So, the best way was to bypass the database. So, that is the answer to your question. Do not use the database. Preload your results into your application and use that. The good thing is now scalability is also very trivial. Supposing my machine can handle may be not 3 lakh, may be 1 lakh. I just set up 10 machines. Now, I can handle 100,000 on each server. Each server has its own copy of the results and I can handle a million users coming in one hour without any problem. Of course, there has to be some more thing which says that request should be rooted to one of these servers. There are a bunch of tricks for this. So, there is one of the tricks used here is that you can have 10 machines with the same IP address and the switch which runs very fast. It is not doing any query processing. The switch just roots packet. Based on the source IP, it may hash and send the packets to any one of these 10 servers. So, the load is balanced amongst these 10 servers, which is done at the level of the network switch. It is not even at the level of a web server. Even before the web server, the network switch does load balancing. Now, each of these 10 servers handles one tenth of the load. If it can handle 100,000 requests in one hour, all million can come in the first hour without seeing any problem. So, that is how you would handle such scale. This particular application was super simple because there are no updates. It is just a lookup. It is very easy to parallelize. When you have updates, there is more effort required to parallelize. So, very timely question because board results are coming out right now and I am sure many candidates in some boards are running into trouble. They cannot retrieve the results. In other boards, they are able to see the results without any problem. If they took the trouble to build a system like this, they would not have a problem. I think some boards tried the tricky solution. I think CBSC said it will announce its results on Monday, but they put up their results on Sunday. So, what happened is a few people somehow found out about it and then others heard from them. So, the load would slowly increase, but it would not be everybody jumping on at exactly the same time. So, that way they probably spread the load. We will take some live questions. We have Bhilai Institute of Technology. Bhilai, please go ahead. Sir, what is the difference between index scan and table scan and which is more effective while going for query optimization? This is actually covered in an earlier slide. So, let me just show you the slide to answer your question. A file scan or also called linear scan simply reads all the blocks of the file one after another and looks at each record and sees if it matches the selection condition. So, this is a file scan. In contrast, linear scan, index scan, sorry, that was the other part of the question, does the following. So, it is called an index scan, but it is actually an index lookup. So, the first step is to go down the index to find the record IDs which satisfy the index condition. So, if I had an ID equal to 1, 2, 3, 4, 5, I will go down the B plus 3 index to find the record ID for records with ID equal to 1, 2, 3, 4, 5. So, I need an index on ID attribute to do this. If I do not have an index, I cannot do an index scan for this particular selection ID equal to 1, 2, 3, 4, 5. Given the index, I would go down, find all the matching records. For the ID attribute, there would be just one record. If I said department name equal to com side, there may be multiple records. So, depending on the case, I get one or more record IDs. Now, using those record IDs, I will fetch the records from the relation. This is what is the index scan. We have Sitaram Bhai from Gujarat. Sir, can you please give us a small example where we can understand the join query easily? So, let me use the whiteboard and give you an example. So, let us say there are two relations. I am abstracting a way to keep it simple. R A B, S B C. I want to compute the natural join of these relations. So, I want to equate the B attribute here and here. Now, supposing I have an index on S B. So, the B attribute of S has an index. Now, I have a number of tuples in R. Let us say the R tuples are let us say 1 A, 2 B, 3 A, 4 C, 5 B and so forth. So, this is the sequence of tuples in here. On S, again I have multiple tuples. Let us say I have A 9, B 7, may be another A 4, C 11, C 12, C 13, C 14, C 15, C 16, C 17, D 21, may be another you know E 31 and so forth. So, this is my S relation and I have an index on this. So, what I am going to do is take the first tuple here from R and find the value of B. So, the value of the B attribute is A here. So, this second attribute is A and I am going to look up the index. So, this is an index on this table. I will go through the index to find all tuples whose all tuples in of the S relation whose B value is the given value A. So, I will search through the index and I will find pointers here to that and to that. So, I have gone through the index and I find two matching records with the first attribute B being with the value A. So, I will take these two, I will fetch each of these and match it and output it. So, what do I get? I will get 1 A 9 and then 1 A 4 those are the two things which I output. Now, I will move on to the next tuple. So, the nested loops is on this. So, for each tuple in R, that is the loop. Now, it is an index nested loop in the sense that for the inner relation S here, I am using an index to find all the matching tuples and then I iterate over them. So, I just did it for the first tuple of R. Now, I take the second tuple of R to B. Again, I use the index to find which all S tuples have the value B for this attribute and here I find this one that is only 1. So, this one matches that. So, the output I get is 2 B 7 is a natural join. So, the B attribute appears only 1 and similarly, I move on to 3 A which again outputs 3 A 9 and 3 A 4 and so forth. So, there are many such results which I generate. I output all of them. So, that is an index nested loops joiner. Biology Institute. Sir, my question is regarding distributed query processing. In that, how to minimize the queried processing time? So, I do not have time to get into all the details, but essentially in distributed query processing, there are extra steps for moving data around. I mentioned briefly the data exchange operator. So, you have to introduce these steps and then there are extensions to the standard optimization algorithms to take distribution to account. I do not have time to get into exactly how this is done, but people have developed these things. So, they will find the best overall plan taking into account the shipping of data from one side to another and the time it takes for that shipping, the time at local sites and so on. So, that is just the high level intuition. I do not have time to get into the details. Do you have any follow up questions? Sir, when we are retrieving large data, the connection will be live because it is connection oriented. Is there any other way of retrieving large data without connection oriented? The question is if you are doing a result set. So, this is JDBC or ODBC. If you are receiving a lot of data, you may not want to hold the connection for the whole time. So, you may want to close it and open it again. So, I do not think that this is supported by JDBC drivers that I am aware of, but there is a related issue here. Supposing I have a very large piece of data blob and let us say it is 1 gigabyte, it is a huge blob. Supposing my JDBC driver insists on fetching that whole blob over as soon as I do r s dot next, it fetches the whole thing over. It is going to fill my memory with 1 gigabyte of that blob and I am going to get into trouble. I do not have enough memory to do such thing. So, what would be done normally is for these large object types, it would not fetch the whole thing over, but as you fetch parts of it. So, how do you do this in JDBC? Somebody had asked this question. So, I have put this up on Moodle actually. I think I sent an email about this or maybe I did not, but I have put up a material on Moodle which explains how to interact with large objects using JDBC. So, how to store large objects on PostgreSQL? There are some non-sanded things in PostgreSQL. So, how to store large objects in PostgreSQL? How to fetch them using JDBC? How to store them using JDBC? And the basic intuition is that in JDBC you have file streams, input or output file streams and you associate them with a particular result. So, when you have done a query, you can do r s dot get input stream on a particular attribute and presumably the implementation should not download the whole data, but should download bits of it. And then as you fetch next on the input stream, it should go back and fetch more parts of the data. So, that should be how it is implemented. I think there were early implementations which did not do this, which would actually die, but I think today those problems should have been fixed and it would fetch on demand. And I think you can also control how much data is prefetched so that you do not have too many round trips to the database. I hope that answers your question. If you have any follow up questions, go ahead and ask. Over to you. Thank you, sir. The next one is Prestige Institute. Indoor, please go ahead Prestige. So, can you guide me about the JDBC connection for the multiple data width? So, let me answer the, maybe I misunderstood it, but the later part of the question said that you are having problems connecting to multiple databases using JDBC and how do you do that? I like I said you should be able to open multiple connections. So, you have to load all the drivers. Some people had problems even loading drivers in the first place because they did not follow the instructions on how to set up eclipse and tell it the parts to the post-criscule drivers for JDBC. Similarly, Oracle or whatever databases you want to connect to, you have to load all those drivers. So, first you have to tell eclipse where these reside. Then you have to use the driver manager dot get connection with the appropriate URL. So, if you are able to open one connection at a time, all of this is working. Then the remaining thing is how do you open multiple connections? So, it should just work. I mean if that is not working, we should you know send me details or post it. When I say send me, please do not email it to me. Please post what is going on, what you did and what are the symptoms. Post it on Piazza. That is the right forum for getting into these details. If something did not work, I am not sure exactly what was wrong with your setting. So, we can follow up on Piazza. On Piazza, the good thing is there are many people ready to answer the question. If you send it to me, I may not answer it. I do not have time right now. Actually sir, we are planning for the virtual e-learning. So, we are planning about the virtual e-learning for that we are creating a data center, a one data center in which the all the data is to be merged. So, we need a JDBC query script for connecting the all the database to each other and then distributed to the multiple system. I think your question is slightly different then. Maybe you want one JDBC connection where you can make a query and your system will forward it to appropriate databases, collect the answers and give it back. That is a much harder problem. There are probably tools for this. Building this from scratch is possible, but it will require a fair amount of work. There may be tools for this. It is not easy because each database has its own variant of SQL. If you are using different databases, if your goal is to get parallelism out of this, where you submit a query to one place and that forwards the query to a number of instances of the same database based on the query parameter. This is essentially building your own parallel database. Maybe this is what you want to do, a low cost parallel database which you build on your own. So, there are projects which have done this. There are lot of people who use techniques like this. It is often called sharding, meaning your data is broken up into pieces across many copies of the same database. Then usually what happens is instead of a single point in between which redistributes, that becomes a bottleneck. Your application will open connections to all copies of the database and then you have a partitioning. So, you say that IDs from in this range are in database copy one, IDs in this other range are in the second one and so forth. So, your application will open connections to all the databases and then if you are querying for a particular ID, it will figure out which instance has that data for that ID and send the query to that instance. So, this is referred to as sharding, where you build your own parallel database from scratch, which is very difficult to do joints across databases and so on. So, this is only applicable in limited context, but these limited context are actually pretty wide. Facebook was based pretty much totally on sharding across many MySQL, some parts of Facebook. Facebook has many things. Some major parts of Facebook were based purely on sharding for many years. I think they are using other techniques now, but this was their key technique. For that matter, Google ads were also based totally on sharding for many years, only very recently have they moved to other technology. I do not know if that answered your question. Over to you. Thank you, sir. We will take one last question and after that I will wrap up the remaining parts of today's talk. This is from Mr. Dishwari regarding the performance part of this whole workshop. I have been personally observing that if we take a look at the execution plan of any SQL query and especially in case of an order by class, we see half the cost is just involved in putting up this order by class. Could you please explain a little about the internals of how this order by class internally works? Number two, should we consider it rather taking out from the SQL and putting in all the applications over? And number third is if we could discuss a little bit about scalability and talk especially about the vertical and vertical partitioning of the data. So there are several different questions. The first question was about the cost of the order by class and that is actually part of today's lab. I am very happy to see people so enthusiastic that they have already done today's lab and are asking questions even before the lab. Fantastic. It is exactly what I was hoping many people will do. So to answer your question, the question was that the cost of the order by class is half the cost of queries for some of the queries which you tried. So I do not know exactly what the queries were but this is not surprising. So order by class is implemented by sorting. Sorting is not cheap. So that is one of the reasons that you know SQL does not provide any default ordering. Moreover, sorting was the primary way of removing duplicates. Since sorting is expensive, SQL decided not to remove duplicates unless you ask for it explicitly. Otherwise every query would be spending its time removing duplicates when maybe it was not required. So it is not surprising that sorting is a major part of the cost of your plan. It depends on the query itself. If your query has joins, then the join cost may be comparable to the sorting cost. Now the next part of the question was given that this cost is high, why do not we do it in the application rather in the database? That is a very bad idea. Database sorting is very highly optimized. It is very good at it. So if its cost is expensive, if you do the plan cost, it is not actually saying it is so many seconds. It is some unit which it uses internally. So it may be a large part of its cost but usually the actual time for sorting is not that high. It is pretty small and if you were to do the sorting in your own application, you would probably be doing it slower than the database does. So do not do it in your application. Leave it to the database. It is not worth it doing it in your application. The last part of the question was, it is actually a different question. It said something about scalability with horizontal and vertical partitioning. So I actually answered this last part of the question as an answer to just the previous question. So we said partition the user IDs across different copies of the same database. So that is called sharding in the industry. However in the research community, this is what is called horizontal partitioning. What we mean is, we have a number of rows of some relation and we are partitioning the relation throwing some of those rows in one of the database instances, some of the rows in another and so forth. So let me show a figure to illustrate this. So if I had a large relation like this with many rows, horizontal partitioning basically says, some of those rows goes to DB copy 1, then some others go to, I am writing B first for some reason, copy 2 and so on. So I am breaking up the relation into pieces. This is called horizontal partitioning. It is also known as sharding in the industry. This term came much later. Horizontal partitioning is a much older word, which has been around for decades now. So this is used very widely for parallelizing database access, especially build it your own parallel database. Meaning that you divide up your data across a number of instances of a database and then you query the appropriate instances when you want to retrieve data. This is very nice when you just want to store retrieved data based on some attribute, user ID let us say. But if you want to do a join between data which is on these two copies, you have to write your own joint code. There is no way to do it automatically. So initially people build their own. Now other people realize that people are doing this and say let us build our own parallel database system, which underneath has many copies of MySQL or PostgreSQL. So interestingly, one of the people who tried to do this was Professor Fatak. About 8 or 10 years ago, I do not remember the exact time, many years ago, he started this project to build a parallel database running on top of many copies of PostgreSQL. This is a very good idea, but he did not have enough people to work on it, who really understood technology in depth. So I do not think that went very far. But around the same time there were other people working on this problem. So there was several companies which have done this. There was a company called Green Plum, which is now part of EMC, which did this. Closer to home, there was a company called Asterdata. One of the founders of Asterdata is an alumnus of IIT Bombay. He was my student here around 2001. So he worked on databases for his BTEC thesis, did a PhD in databases. Then went and started co-founded this company. This company was bought by another parallel database company called Teradata. He is continuing to head the Aster division of Teradata. So this technology has really taken off parallel databases built on top of multiple copies of MySQL or PostgreSQL underneath has really taken off. So Asterdata and Green Plum both used PostgreSQL. In fact there was another project at Yahoo, which also used PostgreSQL. It was an internal project, but at that time they claimed they were the world's largest database at 6 petabytes or something like that. Thousands of copies of PostgreSQL running underneath. So interestingly the largest databases in the world today run on such a system. There are also other things which were developed at Google, which are more recent, which have built their own database system from ground up. But many people found it more convenient to build it on top of existing databases. I hope that answers your question. So very long answer to a short question.