Today we have two theory sessions during which I will finish up on query optimization very briefly and then on transactions and then we have the lab session immediately afterwards after lunch on transactions followed by the last couple of presentations and discussion session logistics and other stuff. Okay, so yesterday in the lab we saw examples of query plans and we had a brief discussion on alternative plans and why the optimizer chose a certain plan based on statistics. So this chapter basically describes some of those techniques which we briefly looked at plus a whole bunch of other stuff and a few people had questions about this yesterday which I would request them to ask again today while I give the talk so that some of the common questions about query optimization which people have been asking can be cleared. So please interrupt me at any time. So the basic thing in query optimization is that given any SQL query we have to evaluate it in some way and there is a bunch of operations which the database supports primitive operations which are basically variants of relational algebra, join, selection, projection and so on, group by. So given an SQL query there are many different relational algebra queries which would give an exactly equivalent answer. Now given a relational algebra query there is another issue of how exactly do you evaluate it? Given a join we saw that there are multiple join algorithms, given a selection you have multiple options, do a relation scan, use one index, use some other index and so forth. So there are basically two levels of choices. The first level of choice which we are going to discuss initially is at the logical level. So given a relational algebra expression there is another logical expression which is equivalent. In fact there are many, many logical expressions which are equivalent and then we go to the physical level which is instead of just saying this is a join we will say that this is a merge join or a hash join instead of saying selection we will say selection using an index and so forth. So then such plans which are annotated with those evaluation algorithms are often referred to as the physical relational algebra or the physical plan. So here is a small example where we have a join of teachers and course then joined with instructor and then there is a selection on department equal to music. Note that this query actually finds all names and of instructors and titles of courses they teach provided both the instructor and the course are from the music department. Why? Because department name comes from instructor and from course and the natural join makes sure that both are from the same department and then this selection restricts it to music. So that is the meaning of this query. Now another equivalent query to this is on the right hand side. So first we select instructors in the music department and then we join it with teachers join with course. Can you suggest another equivalent query for this? Basically the order in which you do joins is not really material they all give the same result. So we could have joined instructor with teachers and then joined that result with course. What about instructor with course? Could we have joined it? We can do a cross product it would still be correct a natural join of instructor sorry it is not a cross product that is a department name in common. So even that is okay but even if it were a cross product it is still a valid relation algebra expression except you would probably not do it because it would be very inefficient. Okay so in this particular query there are many choices of join order. There are many choices of where to place the selection. The projection in this case has to be at the top but in some cases even here in fact you can remove unnecessary attributes early. So for example in this one for instructor are there any unnecessary attributes which we can project out right at the beginning. Instructor has a salary which is not projected out it does not participate in any join so you can throw it out. Instructor has ID but we cannot throw it out. Instructor has name we cannot throw it out because it is used in ID is used in the joins and department name is used so the rest cannot be eliminated. In teachers there is in fact a lot of surface can be eliminated. It has a section ID, it has a semester, year, time slot, no sorry time slot is for the section relation. Here there are three things which can be projected. So basically there is in fact a lot of stuff you can do to remove unnecessary attributes we can do selections early the second version selects on department name equal to music early so that you do not unnecessarily carry around instructors of other departments and do joins only to throw them away at the end. So we can actually come up with a plan which we would think is a lot cheaper but how do we know it is cheaper when that is based on statistics, estimates based on statistics which the query optimizer uses to choose amongst alternative plans as we discussed here. So this is an example of an evaluation plan for the same one. What have we done here? We have actually this one is actually not consistent with the previous one I think one of the plans did not get updated in the slides. So what is this doing here? It is actually projecting course on course ID title and then pipelining the result of the projection to a hash join. The hash join will partition it, it also partitions teachers and then the result of that join is passed up to sorting. Now as we discussed yesterday many algorithms require a sort in particular here we are doing a sort because we are doing a merge join up here. So it is we have to sort this result. Now the result of the hash join can be passed up to sort, this sort runs and then the result of the sort can be passed up to merge. Now it says pipeline, how can you pipeline the result of a sort? Well that is actually because the sort operator can be broken into two parts. One is the run generation and the other is the merge. The merge result can be pipeline instead of being written out. The run generation actually writes runs to disk and then the merge step reads them back from disk and its result can be pipelined up. Similarly on this side we say select department name is music using a particular index which presumably is on department name. And then we sort it because merge joins needs a sort and we are pipelining this into sort. Similarly the output of sort is pipelined into merge join which in fact pipelines it into project. Now can we we are joining here on ID but that ID is being removed by the project. So how do we eliminate duplicates? It sorted an ID which is actually useless for removing duplicates on name comma title. Because many different IDs may have the same name and title. The title is for the course, name is for the instructor. So we actually have to sort on name comma title to remove duplicates or we could hash but this particular project is annotated by saying do it using sort. So this is a complete evaluation plan. Real evaluation plans as we saw yesterday in PostgreSQL actually have even more details than this. So we will leave this over there. How do you view evaluation plans? We already saw how to do it in PostgreSQL using explain. Others have equivalent ways. SQL server has set show plan text on and of Oracle has some version of explain that little bit more work you have to do to get the result. And DB2 has its own. So every optimizer lets you see the plans. Now how do you generate the space of equivalent expressions? We saw an expression, we saw another expression, then we discussed a few more which are all equivalent and some of them may be more efficient than the others. So how do you systematically generate the alternative plans and then pick the cheapest and do this as efficiently as possible without wasting time? That is the key requirement. Now there are actually two answers to this question and different systems have used either one of those answers. The first answer is to use equivalence rules and then have a system which can take as input a set of equivalence rules in some appropriate form and add the run time input to that is the query. It uses the equivalence rules, rewrites the query and by means of finding alternatives using equivalence rules, it picks the cheapest one. This is used in SQL server and Cybase. The other alternative is to not use equivalence rules. This is actually a fairly complex, it was a later addition. Initially people didn't know how to do this efficiently. So although people knew equivalence rules are important, how to use it practically was not clear earlier on. And it was only in the mid 90s that there was a project called Volcano, which actually showed how to do this reasonably efficiently. And then it was improved upon subsequently. So the other alternative which dates back to 1979 when the paper on access path selection is based on optimizing the join order. Because the join order is the most important part of query optimization. There are other issues too. When do you do selections? So that can be handled heuristically. The idea is that why ever postpone a selection? Do it as soon as you can. So based on that heuristic which generally works with some small tweaks. That's basically what the original system are optimized and implemented. And subsequently, pretty much all the databases like Oracle, DB2, PostgreSQL. Everybody implements that algorithm. So we will briefly look at both these alternatives. However, what we won't do is how to show, discuss how to implement query optimization using equivalence rules. We look at the equivalence rules. But how to use the equivalence rules in an optimizer is something which is more complex than we have time for here. We are going to skip that. So let's start by looking at the rules. Logically, it's important to know what is going on. What do we mean by equivalence rules? So two relational algebra expressions are said to be equivalent. If they always generate the same set of results or in fact, in SQL, they should generate the same multi-set of results. What is this multi-set? SQL allows duplicates. So when you run a join operation on duplicates, it in turn generates duplicates. So the count of duplicates is defined in the SQL language. So if the input has so many duplicates, output should have so many duplicates. That is defined in the SQL language basically for each operation. For join, for select, for project and so forth, union. So if we have two alternative expressions for SQL query evaluation, they better generate the same number of duplicates. Otherwise, the results will differ depending on which one you choose, which is not acceptable. So we really want multi-set equivalence in the case of SQL. And an equivalence rule says that two expressions of the same form are equivalent. So let's look at, I'm going to just go straight to the pictorial depiction of a few rules and then I'll come back to this. So the first rule pictorially here says that if I have a join with some join condition theta, joints like this are referred to as theta joints, joints with the condition. Then the first rule says that if I do a join with even as the left input and e2 as the right input, it is certainly equivalent to flipping the order. e2 as the left and e1 as the right. It's fairly clear. The second rule is a rule for natural join, which says, if I join e1, e2 first, then I join with e3. That result is equivalent to joining e1 with the result of joining e2, e3. That's here. What is this rule called? It's called associativity. You're familiar with this from algebra in high school. So that is associativity. The first one is commutativity. Now, I showed this for theta join and this for natural join. But the rule 6b is the same thing for associativity for theta joints. And there is a similar rule for commutativity for natural join. Now here is an example of another kind of rule, which says if I have a selection on top of a join and under the condition that the selection only involves attributes from one of the relations, I can do the selection before the join. In other words, I have pushed the selection through the join and applied directly on e1 before the join. Now, this also should be reasonably clear. If the selection involves conditions from e2 and e1, I cannot do this. Because those attributes are going to be available only after the join. On the other hand, if the selection is a conjunction, if the selection is of form e1.a equal to 5 and e2.b equal to 5 or something as e2.b equal to 6. It is an AND. Then what do I do? I can actually push one part into the left end, one part into the right. So there is also equivalence rules for that. Let us very briefly look at some of the equivalence rules. This time it is not pictorial, it is algebraic representation. So this one says sigma, theta 1 and theta 2 on e is the same as doing one first, actually theta 2 first and then doing theta 1. And the next one says that the result of doing theta 2 first then theta 1 is the same as doing theta 1 first and then theta 2. So now if you do this, you will find that you can actually you can apply it here. If theta is theta 1 and theta 2, you can break it into two. Then push one down and then the other selection will be on top. And then you may be able to push the other one down. So you can use multiple rules to get what you want. Then there are a few more using projections and selections which we pushing selections into cross products that is a select on a cross product is really a theta joint. And in SQL the from clause is actually a cross product and the where clause is a selection and that cross product is turned into a joint and this is the basic rule for that. I am going to not go into every one of these rules for lack of time. But there are several more rules in the book and there are many more rules which are not in the book including a few which are in the exercises and others which are not even there but are actually used. The SQL server optimizer I think uses some very large number. I think couple of hundred rules overall. Now the problem is the more rules you have, the more alternative plans you generate and the more time it takes. So actually what SQL server and other such optimizers do is they will not use all the rules at the beginning. They will start with a few rules and use those to optimize a query. And if they get a good plan, a very efficient plan using those, they won't try the other rules. But if the plan is still very expensive, then they will try other rules. In the hope that maybe one of the other rules can reduce the cost more. So we are not going to discuss all those rules here. This slide shows that you can have multiple rules applied to the same expression to get a more efficient thing. And we have seen this already, so I am going to skip this. So now let's come to the, we have seen the equivalence rules. We are going to leave equivalence rules at that point. And focus on the join ordering problem. So if I have a join which is given like this, R1 join R2 join with R3. With associativity we know we can do it differently. Now, why would these two have a cost difference? That's basically because the sizes of the relations may differ and the selectivity of a join that is when I join R1, R2 what is the size of the output that may differ for R1, R2 and for R2, R3. So for that matter R1, R3. So supposing R1 is, maybe R1 is small. Therefore R1 join R2 is also small, but R2 join R3 is large. So if we use the right hand side here, we are going to spend a lot of time generating R2 join R3 and then join it with R1 which basically eliminates most of the tuples and gets a small result perhaps. In which case, maybe R1 join R2 would have a very small result. If R1 is small and there's a foreign key, the join is on a foreign key perhaps. And then that can be joined with R3 and all the intermediate results are small. And then the join may be much cheaper as a result. There are also other reasons why joins may be expensive or cheap because of indices and so forth. But the join order is really key to this. There's an example here of why a relation may be small. Which is an earlier example which had a selection when department name is music. So music has only a few instructors and there are many departments. Department name equal to music instructor could be very small. Whereas teachers could be very large and course could be very large. So teachers join course would also be large. It's a foreign key joint, but it'll be as large as teachers. And then most of those are eliminated by the subsequent join. So it makes sense to first do the selection instructor, join that with teachers and then join that with course. So I'll leave the equivalence rules back there and see how we can get these join orders. The goal is to find the best join order. Unfortunately, there are many, many join orders. How many join orders are there? Well, if you take arbitrary joint trees, what is the joint tree? It's a binary tree with n leaves, where n is the number of relations which are being joined. Now the question is how many such trees are there? And that answer is basically the Catalan number on n minus 1. Won't get into the derivation, but it's quite large. For n equal to 7, it's already 6 lakhs. For n equal to 10, it is 176 billion. So we really, really don't want to evaluate all possible joint trees. So what do you do? It turns out dynamic programming comes to the rescue and turns ridiculously large number into a large but more manageable number, which is exponential. It's 3 power n or 2 power n depending on what class of trees we consider. And the dynamic programming algorithm can actually be understood as follows. To find the best joint tree for a set of n relations, we can recursively find the best plans for every combination of S1 joined with S minus S1. So we want to find the join of S, which has n relations. We will take every set S1 which is non-empty, strict subset. So S minus S1 is also non-empty. We will take every possible combination, find the best plan for S1, find the best plan for S minus S1, and then find the best way of joining these two results. Now if you, we have many alternatives, every set S1 is an alternative. So if you find the minimum across all sets S1, we would have found the best way of joining the set S. Because you think about it, to get a join of some n relations, there has to be a final join. That final join has to join two sub-results. What we are saying is, let's look at all possible final joins. And for each possible final join, we are recursively finding the best plan for each input. And then, among all possible final joins, we are picking the cheapest. Now this is a recursive thing. So the recursion has to have a base case, otherwise we have a problem. So the base case is when your input S1 or S minus S1 is a single relation. In which case, we are going to heuristically apply all the selections, which are there on that relation and pick the best way of enforcing that selection. That is the base case. Now, if you use this recursive algorithm, just by simple recursive algorithm, the problem is that we will repeatedly call the recursive procedure for a particular subset. For each subset, we will call it many, many times. And the next intuition for dynamic programming is to realize that if we evaluated the best plan for a particular subset once, just store it. Next time you, somebody else asks for the same thing. Why would somebody else ask for the best plan for the same subset? Because a particular subset may be joined with many different relations or many different subsets. So there will be many recursive calls to find the best plan on a given subset and the first time it is called, we compute it using the recursive procedure and save it. The next time it is called, we look it up and return it. This has two names in the literature. One way to look at it is dynamic programming. Most of you who have taught an algorithm score or attended an algorithm score at some point or read a book will be familiar with a version of dynamic programming, which is bottom up. That version computes things before they are needed and then uses them when they are needed. This is actually entirely equivalent, except it is expressed top down. It invokes something, computes it the first time it is needed, saves it. And then on further times when it's needed, it simply looks it up and returns. It's entirely equivalent. So that's dynamic programming. And this is a recursive algorithm for join order optimization. I'm going to skip this details for lack of time. But note that it takes n times 3 power n if we consider all possible joint trees, which is n times 3 power n is large. But it's much, much smaller than the Catalan number we saw before. So for example, for n equal to 10, what do we get? 10 times 3 to the power of 10. What is 3 to the power of 10? I don't remember the exact number. We can calculate it, but it is large, but it's not as large as 176 billion, but still it is large. Therefore, many optimizers don't consider the space of all possible joint trees. But they restrict it to what are called left deep joint trees. What is a left deep joint tree? It's one where for every joint in the joint tree, one of the right input is a relation. So it will look like this. In contrast, this is not a left deep joint tree because for this joint, the right input is a joint result. Now, if you only consider left deep joint trees like this, we can, in fact, modify the previous algorithm a little bit. There are some details in the slides on how to do it. It's actually very straightforward, but the cost comes down quite sharply. From n times 3 power n, it becomes basically 2 power n. And what is 2 power 10, 1000, that's not bad at all. If we do 1000 operations and we can find the best plan for 10-way joint, that's pretty good. Now, again, any algorithm's person will say, but this is exponential. We really don't want exponential algorithms. And it's true that if you have a joint which has 30 results, 2 power 30 is very large. That's going to take a lot of time, so billion. We don't want to spend that much time or space for that matter. So, pretty much every query optimizer has some heuristics built in. If the number of relations gets excessively large, up to 15, 16 relations, they will use this algorithm which we just described, which takes 2 power n time. But if it gets even larger, they use heuristics too, which will take less time. And cut down the time taken so that you don't take 2 power 30 times, for example. So, we won't get into the details, but all optimizers have this, so that you can't mess them up by giving a large joint query and then get them into trouble. Yeah? Are there real that large number of relations really? Yeah, that's a very good question. Do you ever have such large relations? The answer is no, typically you don't, which is why almost always. So, if you look at our schemas, the toy schemas we have don't even have that many relations, but you can keep joining a relation with itself, for example. Like we saw in the station, the track case, we could keep joining. So, the first answer is, realistically, these are very, very rare. Hardly ever does anybody ask a query with more than six sum in joints? If you have views, this may go up. The views get expanded, and it may go up to maybe 10, 15 joints. These do exist in the real world, but 30, they are rare. They can happen. People do write humongous SQL queries. In the industry, there are horribly complex queries in some many cases. So, what the, you don't want is the database dies if you ask such a query. So, the database has to protect itself from such queries. So, they're very rare, but occasionally they do arise. And then you may have an evil person who takes pleasure in showing that the database dies by creating a very large, meaningless joint query, which is very large. Okay, so this slide talks of the space and time complexity of optimization for joint order. And it also talks of how to handle left-deep joint trees. Okay, sorry, I said the time complexity is 2 power n. It's actually n times 2 power n, which is still not too bad. 10 times 2 power 10 is still 10,000, it's not 1,000, but it's pretty small. Okay, now there are some more details to do with interesting sort orders. For lack of time, I'm going to skip it here, but I'll just briefly mention what it is. The previous slide assumed that if I'm given a set of relations, I want the best plan for it. The thing is that different plans may give different sort orders on the relation. And if the relation is unsorted, if I want to do a join, I may have to then do a sort to do a merge join, or I may have to do a hash join. On the other hand, if some join operation below happened to use merge join, it is possible that the result was sorted on a join attribute, which means I can use merge join directly without a sort. So the bottom line is that previous algorithm is not quite right because the way in which a relation is sorted affects the cost of future joins. In other words, I don't want just the best order, best plan for a given set of relations. I may want to get the best plan for each of several useful sort orders. So if I have a join, I may say, give me the best plan sorted on the join attribute. If I'm using a hash join, I may say, just give me the best plan. I don't care what is the sort order. So that procedure can be modified to take an extra parameter, which is the sort order, and still dynamic programming applies, all the good stuff applies. And we can modify it to take this sort orders into account and then get the best plan. And you can either do the top-down version, which we saw, or the bottom-up one, which system are used, actually has a way of pre-computing what are all the sort orders which are useful later, and will generate the best plans for each of those sort orders. For both variants work, I'm going to skip the details. The next set of slides is on statistics for cost estimation. For lack of time, I won't get into the details here. You can read it up later. I'll just mention a couple of things. One of the things is for estimating the size of a selection. If we have several different ways. One way is if we have a selection on equality and we know the equality is on a key, we know there's only going to be one result. If the selection is on something which is not a key, how do we know how many results will be there? Even for a simple selection of the form a equal to five. How do you know how many tuples have a equal to five? We saw an example yesterday where we had a rating and the database knew there are only five distinct values for the rating. It computed this statistic. And then when you say select where rating equal to four, it estimates that one in five tuples will satisfy that. So if it knows there are 100,000 tuples, it could estimate that 100,000 by five, which is 20,000 tuples, will satisfy rating equal to four, or rating equal to one for that matter. Now, this assumes uniformity. What if the ratings are not uniform? What if the rating of four or five is rare? Let's say movie ratings. How many movies have got a rating of five on five? You've seen these movie ratings in newspapers or websites. When was the last time you saw a movie with a rating of five? It's very rare, critics never like to give five. They do give four, but four is rare. Three is more common, two is common, one may be less common. Zero, nobody gives zero typically, let's say zero is not an option. So there is a non-uniformity. Now, if I have a histogram, which gives the frequency of different values, given a particular selection, if I say rating equal to five, I can get a good estimate that it's a very small number. Whereas, rating equal to two or three will be a much larger number. So that's where a histogram comes in useful. It helps us to avoid the uniformity assumption. There are different kinds of histograms, for lack of time I won't get into those.  And I'll just spend a minute here on joint size estimation. The most common kind of join is a foreign key join. And here, size estimation is easy. What is the size? If I join a relation with another, and the join attribute is a foreign key from one referencing the other. I know it'll be exactly the size of this relation. So that is very easy. But what if there are selections on this? What is the selection on the referenced relation? What is the selection on the referencing relation? Then it becomes a little more complex. We can still use the information about foreign keys and get a better estimate. But if it is not a foreign key, if it's a join on some pair of attributes, which is not a foreign key referencing in either direction, then how do you know what is the size of the join? Then so again, if you know the number of distinct values, you can get a good estimate. So let's look at these two estimates. Now, we assume that the join is on an attribute A, that's a common attribute, we are taking a natural join. Now, V of A comma F is an estimate of how many distinct values there are for attribute A in relation S. Now, what is this estimate? How do we get this estimate? For each tuple in R, how many tuples on average is it going to match in S? And the answer is if S had 100,000 tuples and there are only five distinct values, each of those values is going to occur 20,000 times. And if you did a join on rating with some other relation, then each tuple in this relation we will estimate matches 100,000 by 5, which is 20,000 tuples, that's a reasonable estimate. But you may also, you will notice that this is entirely symmetric. Why should I use only the estimate for S? I may, symmetrically I can say, I take a tuple of S and see how many tuples of R it matches. And that estimate will be nR divided by the number of distinct values of A in R, that is this estimate. So, I have two estimates for the same thing and it has been shown that the lower one of these is more likely to be the correct value. So, that is how you do join size estimation in general. Again, you can do better if you have histograms on both sides and so forth. We are going to skip the details and there are more details in the book for estimation for other operations. It's not just the size estimation, it's also the estimation of how many distinct values there are, why do we need this? As we just saw, to estimate the size of a join, we need to know how many distinct values there are on the join attributes. Supposing I have a join which looks like this, R1, R2, maybe R3. To estimate the cost or the size of this join, I need to know how many distinct values there are, let's say this is on attribute A. So, I need to know how many distinct values there are for attribute A in this join result. So, I need to figure out how to estimate that also. I cannot compute any of these exactly. They have to be estimate statistical, because I can't run the query to find it. I'm looking at a huge number of plans. So, I have to quickly estimate these numbers. So, the book has details on how to estimate the number of distinct values of a join result, of a selection result and so forth. So, we will wrap up join optimization and cost base optimization in general with that. If there are any questions, you can ask me. How to decide what is the equivalence to? So, first of all, we can take each operation or combination of operations. Many of the equivalence rules, yes, there are a lot of rules. So, your question is which rule do we use? The answer is that is some sort of minimal rules which optimize like SQL server. It would use those rules always. Now, what are those rules? The rules we have given are essentially they are almost minimal. There are a few redundant rules there. We have not given a very large number. We have given about a dozen rules. That is not considered very large. That is a small set of rules. In fact, we have not given many rules. There are a lot more rules which apply to aggregation operations which we have not given. So, if the query does not have aggregation, that rule is not useful. So, it will be eliminated, it will not even be used. If the query has aggregation, that rule may be included and then the optimizer would find, will use some set of rules. Like I said, there are more complex rules which apply very rarely. Those will be used only if the basic set of rules doesn't produce a good plan. What are these rules common to aggregation operations? No, these database are fixed. First of all, there are only two commercial optimizers which use transformation rules. One is SQL server. The other is some version of Cybase. And there are some research prototypes also. There's one which was built in IIT Bombay, which is actually available in public domain. That also uses that. And Volcano was the original one, which started off the whole thing. That's a very old code base, but I think it is still available. So, each uses its own set of rules. But all the rest do only join order optimization. And then they use some heuristics or some of these transformation rules used as heuristics. What do I mean by that? A transformation rule does not say if this is equivalent to that, you must use that expression. So, you can use either one. Consider both alternatives and find the best plan. The same rule can be used as a heuristic, which says if it matches the left-hand side, always rewrite to the right-hand side. So, if the optimizer implementer decides that almost always the right-hand side is going to be cheaper than the left-hand side, then you can use the same rule as a transformation rule heuristically. And many optimizers use such heuristic rules. They do join order optimization to consider all the join orders. But to handle other operators, they use some of these rules. Any other questions? Approximation algorithm for a join order. That's actually an excellent question for the benefit of the recording. Join order optimization is known to be an NP complete problem. And that's why those algorithms we saw are all exponential. So, are there good approximation algorithms for this? This question was actually an open question for a very long time. Till some years, how long? About five, six years back, Prasad Sumit Ganguly of IIT, Kanpur had a paper which showed that you cannot even approximate it. It's actually a very hard problem. So, that was actually a very nice result and that's from here. From IIT Kanpur. So, what is used in practice industry are heuristics. So, there's no guarantee, they are heuristics. There are, like I said, if the number of relations becomes too large, you use heuristics to cut down the cost. So, some of them are polynomial heuristics. Some of the heuristics are not polynomial, but they will at least reduce the exponential from 2 power 100 to maybe 2 power 20 or something like that. So, it's a more manageable number. I assume everyone knows what is an approximation. So, approximation is you get a plan who's cost is within some factor of the optimal. You cannot even get to it within any constant factor of the optimal always. That is what it means. That to say it cannot, the problem cannot be approximated in polynomial. So, the optimization is, and transformations are one part. It turns out that SQL as a language has some features which cannot actually be handled directly in the simple relational algebra we have seen. In particular, nested subqueries cause a problem because they cannot be represented directly in relational algebra. So, the question is how do you optimize nested queries? And there are two answers to this which are used in practice. The first answer is to extend relational algebra a little bit to handle nested queries, but don't extend the optimizer correspondingly. Instead, we can rewrite the SQL query to remove nested subqueries wherever possible. So, this is a heuristic which most databases have some form of it. Now, how good is this depends on the database. So, for example, in PostgreSQL, I would request you to try this out if you can. I'll add it to the set of exercises. We can give a nested subquery and find the execution plan for it. If you give a simple nested subquery, PostgreSQL might actually transform it to a join or a semi-joint. But if you give a more complex nested subquery, PostgreSQL will not be able to do anything. And it will actually run the outer subquery and for apply the other join conditions. And then, so let me show an example of what I mean by this. We have a nested subquery which is a select name from instructor, where exists select star from teachers, where instructor.id is out of one equal to teachers.id and teachers.year equal to 2000. What this does is a simple nested subquery which has a correlation variable. It's using instructor from outside. It is using it in the inner query where clause. It's actually a simple query. It just finds instructors who have taught some course in 2007. And if you give this query to a human, all of us know that we can alternatively do this using a join. An nested subquery is not really required. We could have instructor join teachers where teachers.year equal to 2000. There is a slight difference. If I do just instructor join teachers, I will get duplicates. So if I want the correct duplicate count, there is a little more work has to be done. Let's ignore that for the moment, it can be done. But the bottom line is how does the optimizer transform a nested subquery like this to a join? And the answer is there are, again, equivalence rules effectively which can be applied to transform this into a join. And the intuition is that joins are likely to be much more efficient than a nested subquery. Why? Because a nested subquery asses will be evaluated once per outer tuple. If the instructor relation has a large number of tuples, the inner query is executed many times. Each execution of the query may do some random IO. So overall, you may get a huge amount of random IO which will make it very slow. In contrast, if you could transform it to a join of instructor and teachers, maybe we could use a merge join and do this very efficiently with very little random IO. So it's a good heuristic to transform nested subqueries to joins wherever possible and to avoid what is called correlated evaluation. The default in SQL is correlated evaluation. We can actually write this in relational algebra as follows. What we have is a selection on instructor. Where the selection condition theta is really this involves a subquery itself. And that subquery is basically this nested. So this one corresponds to, it has a select and the condition is exists, select, teachers and this condition here is the year equal to 2000 and teachers.id equal to instructor.id. So what that means is instructor.id is being passed down here to this selection. So this is not a traditional relational algebra. It's an extended relational algebra. But that is what is used to evaluate SQL. So this is correlated evaluation. If you run it exactly as is. Yeah, there is a dependency. So what it's doing is it takes this result, result of whatever sub expression here in general. For each tuple here, it invokes this side and evaluates this whole sub expression once. Passing in any parameter, in this case, the theta here is instructor.id equals teachers.id and year equals 2007, that is the condition evaluated there. So it's actually invoking a relational algebra expression many times, one per outer expression. No, it cannot be cyclic because the subquery is in the var clause here. So the dependency is always the variables from the outer query can be used in the inner query. Dependency cannot be cyclic. So now how do you do this rewriting from nested subquery to join? I won't get into details. But that is a small example of rewriting which actually preserves the number of duplicates correctly. So what does this do? First of all, it creates a temporary table from teachers where year equal to 2007 and selects distinct id. So each instructor is stored only once, even if they taught many courses. And then that is joined back with instructor. This temporary table T1 is joined back with instructor on T1.id equal to instructor.id. And this will ensure only instructors who have taught a course in 2007 come out. It ensures that there are no duplicates simply because T1 does not have any duplicates. Now instead of doing a create table, you could have used a width clause, which is equivalent. But that's essentially what the actual decorrelation. This is called decorrelation. And there are a number of transformation rules for decorrelation which we won't get into at this point. And to wrap up query optimization, let me mention the idea of materialized views. We have discussed this earlier, I think. Materialized view is a view whose results are actually computed and stored as opposed to a normal view where the results are never stored. They're computed as required and thrown away. In fact, they're not even computed fully. With a normal view, if I have a query which uses a view, it replaces the view by its definition. And then it optimizes the query. So the actual view may never get computed. The optimized query may move some relations here and there. So the view definition now gets moved around and it's never actually computed. In contrast, the materialized view has been computed and stored. So what are the issues here? Why would you do this, first of all? The most common use for materialized views is to pre-compute aggregates. So supposing I have an analyst who wants to know what are the sales of a particular product in a particular region in each month of each year, that's one kind of aggregate query. Now if the sales relation is very large, if you take any large retailer, Big Bazaar, any of the large electronic shops, these guys have huge volumes of sales. Each of them would be selling millions of items a day, especially something like Big Bazaar. Now if you run an aggregate query on hundreds of millions of items, it's going to take a long time to run. So if the analyst gives a query and then has to wait for an hour or two to get the result, it's not a very efficient use of the analyst's time. So what you would like is to pre-compute all this before the analyst even looks at it and then the analyst can just retrieve the pre-computed result. And that's where materialized views are most useful. So you'll create materialized aggregate views, which are maintained. So every day new sales results come in and the view is updated. So that's a typical use. So the question is how to incrementally update the result of a view as new records come in. Again, we don't have time for the details, but the book has some details on how to do this for individual operations. If I have a group by operation, how to incrementally maintain it. When new tuples come, how do I update the result of the group by? When I have a join and new tuples come, how do I update it? But it's not only new tuples, I may also delete tuples. So delete tuples, how do I update it? I may even update a tuple, in which case, how do I change the materialized view? All of this has to be taken into account and the book again has details on how to do this for individual operations, as well as for an entire query, because a materialized view is a query with multiple operations. So if any underlying relation changes, I have to compute the change to the view result and update it. So how to do this is described in the book. I'm going to skip details here. And the final thing was a question which somebody asked yesterday, which is given that we have indices and if you have materialized views, the problem is correspondingly more complex. What indices and what materialized views should I create? And the answer is it depends on the workload and how do you get the workload? Many database, in fact, pretty much all databases let you turn on the parameter which logs all the queries which are executed. So you get a log of all the queries which ran over some period of time. You can use this to determine which indices to create, which materialized views to create. You can do it manually, that's very hard actually, or you can use a tool which is provided by most of the commercial databases called an index tuning wizard or database tuning wizard or they have different names for this. Whatever the tool is called, it's all major databases have it. Postgresquial doesn't, it's not commercial, but the commercial ones have it. You can use that tool to help you make this choice. Any questions? There's some other optimization techniques which are there in the book. I'll skip the details. And that wraps up our session on query optimization. And it also leaves us about two and a half hours for transaction processing. So obviously I will be going fast over that. Are there any questions before I switch to transaction processing? Of course, the multi-query optimization. So if you get a set of queries, how do you optimize them collectively? And it turns out this dynamic programming algorithm which we said that the best plan for something is based on finding the best plan for the subsets. It turns out when you have a set of queries, this doesn't quite work. So dynamic programming is based on what is called the principle of optimality, that the best plan for something is composed of the best plans for its sub-parts. The problem with multi-query optimization is that you may have a plan which is sub-plan which is not optimal, but it is actually shared by many different queries. So it makes sense to use that for this query rather than the optimal plan for this query. That actually has a lot of repercussions. It makes the problem of optimization a lot more difficult. So the dynamic programming actually has to change significantly because of this. And it becomes very expensive, which is not practical. Therefore, we need some heuristics which are reasonably efficient and work well in practice. And implementing those heuristics itself turns out to be non-trivial. So it requires some bunch of clever tricks to make them work efficiently, even the heuristics. So that problem has been addressed including some work from here where we looked at how do you, what heuristics make sense and how do you implement the heuristics efficiently, which itself turned out to be non-trivial. So we did some work about 10 years back on that. And multi-query optimization has turned out to be quite useful in many domains. So I don't know if anyone is using exactly that implementation, but it is being used in some context. Yeah. If you look at the commercial applications nowadays, which are using huge databases, then you will see that creating the most databases of the data rather than going and writing the databases after some time. So if you look at this real-only kind of the operations, do you think that the relational model is useful? Thanks, that's very nice, Hal. What about the optimization and... That's a nice question, which is forcing me to talk about something which I did not really cover in these things. So the question is that a lot of applications today require data analysis as opposed to updating of data. So in fact, in general, the database application world is split into two parts. One is called OLTP for online transaction processing and the other is called decision support system of which one aspect is OLAP, which stands for online analytical processing. There are other aspects to decision support also. So OLAP is something which I briefly alluded to just a little while back where I said an analyst needs answers quickly for aggregate queries. And how do you support that? Basically OLAP systems are designed to pre-compute a number of aggregates so that whatever question the analyst asks, either the answer is already computed or it can be efficiently computed from one of the already computed answers so that you never have to go back to the underlying relation, which the online part is basically because the answers can be given online as opposed to telling the analyst, okay, I've accepted the query, come back tomorrow I'll give you the answer, then it's not online query. So coming back to the specific question, it was should you use traditional designs for relational databases for the problem of decision support? And the answer to this question is there are certain decision support queries where the traditional databases are fine, but there's also other representations. The relational model is not changed, but the underlying implementation is changed. There's something called column stores which have been shown to be quite useful for many decision support applications, not all but many. And what is a column store? If you take a traditional database, as we saw, we have a notion of pages or blocks and then there are records inside it. And all the attributes of a record are together. Now what many people realize is that for some of these decision support queries, you have records with many fields, many of which are not used for many queries. Most of the queries access only one or two fields. Now if you have to read a page with all the other fields, it becomes quite slow. So they said let's push this representation to column representation, which essentially looks like this. So you store a file which has the attribute. So let's say this is R of A, B, C is a relation. So what they do is they store in a file attribute A of all the tuples. They store in another file attribute D of all tuples and in a third file attribute C of all tuples. Now what have we done? Instead of storing records, normally we have cut them vertically and stored for a particular column, we are storing the values for all records together. So we have reclustered the thing instead of row major, we have done column major representation. Now why is this useful? If I don't want to access B and C, I only want to access A, I can access one file which is one third or maybe even much smaller than the whole thing. The second trick which is used is that once I have all these values which are from the same domain, I can use compression much more effectively because they're all from the same domain. So a file which would have been in raw form which would have been 100 megabytes. Now all of those are integers which are age. Now we can optimize this, we can compress it very effectively to be instead of 100 megabytes, it may come down to five megabytes. Some of these are compression techniques might have applied even in the traditional row representation but they're not as effective because there are many different kinds of attributes stored together so the compression is not as effective. So by combination of avoiding reading things and compression for many queries, column store can be much faster, even five to 10 times faster than a relational, the row oriented representation. So people have responded in different ways. Some people have gone and built an entire database using only the column store representation. Others have taken the traditional database and added indices which are really column stores. So they continue to keep the rows but they have an extra column store which is like a materialized view or an index on the original relation. It's an index really and yet others have looked at alternatives which combine within a block, they will store tuples column wise. But all the tuples, all the attributes for a particular set of tuples will be together on one block. So it's a not exactly column major, it's not row major, it's more like a zigzag order. So different variations have been tried out in practice and implemented in different systems. I hope that answered your question unless you had something else. Unstructured data. Unstructured data. We have, nowadays, looked at the Google and as a unstructured database. Okay, so we understand this question is, so we have this relational model which has a very strong structure. We do detailed schema design but there are many applications where the structure is not as firm and people want a flexible structure. So today, let's take users. So any company like Google keeps user profiles. Now what are the attributes of that user profile? So there are some common attributes, login name, name, age or date of birth, whatever, location, a few things which are going to be common for all users. Now there are users of different, what are called properties or parts of the Google empire or the Yahoo empire. So each of those may want some extra attributes to be stored about that user. Like what are the set of friends of this user? It's something which one component wants to store. Another guy may want to store something else. So the net result is the set of attributes may change dynamically and it's different for different users. So these are called flexible schemas. So now there has been work on two fronts. One is, for example, SQL server itself internally, at least in the prototype. I don't know if it has been released yet in 2008 version. I think it may be there in the next version. It has support for relations where you can add attributes as you please. It can have thousands of attributes and yet it will be stored efficiently. You can dynamically add attributes that won't cause any problems. So that's an example of a flexible schema. And of course, non-relational databases like the data stores used in Google, Yahoo and pretty much everybody else, all support flexible schema which let you add attributes on the fly. In fact, some of these use what is called JSON. If you're familiar with JavaScript, JavaScript has a notion of objects. But unlike traditional objects, JavaScript objects can have as many attributes as you want them. JavaScript is naturally a flexible schema language. And it has a storage representation called JavaScript object notation, which is a storage representation for JavaScript objects which have flexible schema. So this is becoming quite popular. Many of these databases now let you directly store objects in JavaScript object notation and retrieve them. So when you store it from JavaScript and retrieve from JavaScript, it's actually very efficient. Of course, if you do it from PHP, there are converters which will take a PHP object and convert it automatically to a JavaScript object and let you stick it into one of these stores and conversely retrieve it and get it out as a PHP object. Any questions, any more? Thanks, I think there will be a good set of questions.


Hello and welcome to the Groundline Review, your source for everything One Piece and today it is my pleasure to present the review of a lifetime as we examine chapter 957, Ultimate. So I know that the internet has been set ablaze with this and it's for good reason. Look, I've only been in the YouTube game for a couple of years but I've been reading One Piece weekly since 2005. I was there when the existence of the Four Emperors was first revealed, I was there when the Straw Hats were defeated on Sabotee, I was there when Ace died and I was there for the insurmountable hype that was the very first post-timeskip chapter. And I have to say that this chapter kind of eclipses all of those moments. Actually, you know what? Let's put it this way. Just like last week, spoilers for this chapter were pretty unavoidable except when I saw them, I was 100% certain that they were fake.  Cool. Oh, and Shanks as well. Yeah, right. Oh, and Roger and Whitebeard. Sure, sure whatever. Look, I'll just wait until the real chapter comes out. But as it turns out, this chapter was every bit as insane as that and more. So the main focus of this chapter was the Rocks Pirates and I'm thrilled to finally know a lot more about them. Ever since the name Rocks was first dropped, the Vocal One Piece community have been losing their minds over this group with billions upon billions of theories and speculation. Most of which I haven't really been on board with because we had next to nothing to go on, but my God, this chapter. Apart from raw numbers, one of the biggest surprises that came out of this chapter for me is the fact that Whitebeard was a member of the Rocks, which seems like a very out of character move for someone so caring and compassionate when you compare rather than no members such as Big Mom and Coyote. I mean, actually, even in this very chapter, they state that the Rocks were absurdly violent and vicious. A group that disposed of allies and enemies alike. But I think it makes a lot of sense for Whitebeard to have been part of this crew. He was a rough and tough orphan boy who was looking for resources to support his home. And as a result, I imagine that joining this crew would have been a quick and easy deal towards that end. Plus, it's also mentioned that no one in the crew really got along with one another, so why not? Another legend to add to the book of Whitebeard. And while we're on him, he was one of the numerous bounty reveals this week, with a truly staggering 5 billion and 46 million berries. Really does make Luffy's 1.5 and even Blackbeard's bounty look like a teeny tiny achievement in comparison. Kind of like a participation award for running a race. I mean, I have no idea what I always pictured Whitebeard's bounty being, but I think I've always been mentally captured around the 3 billion mark or just over for the pinnacle of cash reward in one piece, so this chapter has very much shattered that perception. And I guess we should now segue into everyone else's bounties because we're here now. Every page of this chapter was pure hype, but moving from world figure to world figure was an incredible experience. So we start at low with Blackbeard. I mean, I say low comparatively compared to the craziness of this chapter. No new information there except for his current Hachinoso Island being the location of the origin of the Rocks Pirates. Which is very interesting actually because it would appear to make that island somewhat more important than I've previously suspected. And I do wonder whose control it was under prior to the Paramount War. I guess it's also worth flagging the name of the island once again because the official English translation for it is full of lead island. And that seems incredibly weird at first and I believe that most of the scans refer to it as Beehive Island which is taken literally from the Japanese word Hachinoso. However, in Japanese, Beehive can also apparently be used to reference bullet holes and that the official English translator has stated in the past when the island first appeared that its name translates more literally as has been shot up. So as a result, they've counted with an English phrase being full of lead or full of lead as it will now apparently be known. And just as when I read this during chapter 925 I think that this sounds a bit weird. The Beehive Island also doesn't work pragmatically at all. So I'm sticking with the Japanese Hachinoso for now. It's just simpler. Last thing on Blackbeard for now though, I like his wanted poster image. It's a solid angle with some nice facial depth and I love that he's spiking the camera as well. Like he's completely aware that his bounty photo is being taken and he wants to look as menacing as possible. It's so much better looking than any of the fan made wanted posters for him which actually goes for everyone else revealed here as well. Next up we had a huge leap with Shanks, a man who is apparently worth 4 billion, 48,900,000 berries. And my God, that escalated quickly, didn't it? What I love about his poster is that it's the antithesis of Blackbeard's photo. Shanks is acting calm and cool, probably entirely unaware of the camera pointed at him and if he is then he simply doesn't care. It's a simple but brilliant image which very much sums up Shanks' aesthetics in general. As for the number itself, like I said before I'd never really imagined a bounty in the 4 billion region. So this was very much an amazing shock to my system. It's somewhat nice to know that despite the fact that Blackbeard is officially recognized as one of the emperors he's still not considered on the level of the previous establishment. Nor should he be because he is a member of the worst generation and more of Luffy's contemporary than anything in my mind. What did kind of shock me is that Shanks' bounty was lower than that of Big Mom and Kaido but I think that's entirely because there was an article that came out in V Jump early this year which teased that Shanks' bounty may have been the highest of the current emperors. But I feel like this is one of those things that got a bit lost in translation and the fan base including myself by the way extrapolated far too much on. In reality though I'm quite happy with Shanks' numeric position here because he is not part of the same generation as Big Mom, Kaido, or Whitebeard so I feel like it does make some sense to have that gap here. The most interesting part of Shanks' segment though is that we now have a timeline on when he actually became one of the emperors which was six years ago meaning four years before Luffy set out on his journey. Now this has probably miffed a lot of people because in the One Piece Fivio Card data book there was a great big question mark placed on exactly what stage Shanks became an emperor and it was arbitrarily plonked before he met Luffy. Now in my video covering this information I went to great lengths to state that we really should not be reading into that too much and a lot of people bombarded me with very stern comments and messages stating that Shanks was clearly an emperor by the time he met Luffy based entirely on the vaguest possible information. And so I'd like to offer a quick I told you so and move on. Because next up we've confirmed Big Mom's bounty which sits at 4,388 million berries. Other than that there's not a whole lot of anything revealed about her. It's just restating what we already know but her wanted poster is the stuff of nightmares. I could not imagine being the photographer who was charged with taking that photo because it looks like immediately after it was snapped they were devoured by Charlotte Linlin. Terrifying stuff. And Kaido's photo was equally as fear inspiring with this look of pure rage that he has on. Once again good luck to the photographer in that situation because it looks like he's about to go on a rampage but I think this sums up Kaido quite well and his bounty of 4,610,100,000 berries is more or less expected at this point in the chapter. It's really not a massive reveal after having seen Shanks and Big Mom because we know that Kaido in terms of power is roughly equal to Linlin but that he is much more of a threat to the world because if his whole desire to plunge it into war so you know seems about right. I mean let me put it this way of all the bounties revealed in this chapter this is the only one that didn't make me absolutely flip out because once you've already revealed the rest of the four emperors I kind of knew what to expect. What I absolutely did not expect was for the bounty news to continue into the legacy era. I've already spoken about Whitebeard but just on his bounty image this is probably my least favorite of all of the posters revealed this week. He just looks super casual and I don't think it exudes his core character as much as everyone else. Like for example the final and most shocking bounty reveal in this chapter being a certain goal Dee Roger. His picture is pretty perfect. I know that we know very little about Roger but this photo sums up the unstoppable force that was the pirate king and the bounty of 5,564,800,000 berries is quite the mouthful and just well. That number really does a number on me just trying to imagine what he was capable of to be worth that because for a long time I've been operating on the idea that Roger was nowhere near the power of his contemporaries like Whitebeard, kind of a big mom but that he was instead more akin to Luffy. Powerful but modestly so and that his world shaking talents like primarily in other areas such as being able to make easy allies and having fate on his side. After seeing this as well as the other information dropped in the chapter I think I may need to seriously reevaluate that thought though. But before we get to that other very important information I just want to talk about the sheer madness of having a mass reveal of bounties like this and to be perfectly honest as Gloria says it made the chapter I do question whether or not it was the correct course of action from a narrative standpoint because here's the thing it does not take this many reveals to be considered a world shaking chapter like the one we had in the previous interlude which just gave us Blackbeard's number. That was enough to set the world on fire. So I could see a variation of this chapter in which only Big Mom and Kaido's bounties are revealed which would probably still be considered the best chapter of 2019, no contest. But my wariness here comes from two factors. One is that I'm very aware that it is possible to have too much of a good thing and a sort of temporary bounty fatigue may set in. Like this information is stunning but there are so many different bounties to process that the individual impact of revealing them is somewhat lost especially when you then immediately move to a bounty that eclipses the former one examined. I mean, for example, very few people are going to be discussing Big Mom's bounty at great length because it's just absolutely nothing compared to oh my God, Roger and oh my God, Whitebeard. And the other thing that makes me pause for thought is that this chapter has finally put a ceiling on the one piece world. We now know exactly what kind of number it takes to be considered a top tier pirate in the series and I for one have really enjoyed experiencing the past 950 plus chapters without knowing what the pinnacle of the series is. Thinking about it now there is really only one bounty that I'm excited to see in the future and that is Dragons who is known as the most wanted man in the world and had that title whilst Whitebeard was alive. So from that we can gather that it was over 5 billion. But thinking about people like Warlords of the Sea whose bounties were mentioned to be updated in this chapter I almost don't care at all because whatever they are they're going to seem underwhelming compared to the landslide of information we were given here. I mean, even Mihawk isn't going to be that exciting. Let's say that his bounty is updated to like 3 billion berries or something. That is a massive amount and under any other circumstances prior to this chapter it would have been worthy of the utmost hype. But after seeing all of this I think the bounty reveals going forward are always going to be kind of underwhelming. And one I question most is why show us Whitebeard and Rogers bounties? You want to show the other four emperors? Sure. They're relevant. So that's fine. But why provide such legendary information at this point in the series? I mean, yes, it was great to find out but arguably highly unnecessary and may even do a bit of damage to one piece going forward. So I do think this information could have been portioned out better in the same way that this information has been beautifully and efficiently portioned out over the last 22 years. But that's enough of that. I'm sick of talking about bounties for now. So let's get back into some major, major hype and take a look at the absolute boss that is Monkey D. Garp. My favorite panel in this chapter by far was the brief flashback of having him overcoming the rocks. There's something really magical about seeing a young Garp having gone all out because it just sparks the imagination like crazy. And I love this image of him holding out his bloodied fists still wearing his Rackard Marine outfit with a loose tie and everything. This is the stuff of legends. Like at the moment, what I want more than anything in this world is a commissioned Renaissance painting of Garp standing like this victoriously on God Valley. It's just such a strong image and well worthy of being entitled The Hero of the Marines. I also really love the timing of this information because I recently did a top five strongest Marines list and spoilers Garp ended up being number one. And there were more than a few commenters saying that Sakazuki should have been number one. But after looking at this, I don't know how anyone could believe that. Yes, it was 38 years ago, but Garp is still on a whole different level to any of the admirals or even the current fleet admiral. What I can't ignore any longer though is the even more exciting idea that Garp had to team up with Roger to dispose of the rocks pirates. This goes beyond the stuff of legends and almost into the realm of high fanfic. The amount of cash monies that I would pay to see Garp and Roger as allies against these figures is embarrassingly high, but at the same time, I'm quite satisfied with the knowledge that we have here. I'm a big proponent of not showing too much because I believe that by and large, our imaginations are much more powerful than anything Oda could show us in terms of action, which is one of the reasons why I love that we've never seen the Sakazuki vs. Kuzan fight because I don't think that it will ever rival the legendary thoughts that we've all had about it. But what's really bizarre about this conflict is the reasoning behind it in the first place with Garp and Roger allegedly joining forces to protect the celestial dragons as well as their slaves. And I find it very interesting that Sengoku felt the need to add the part about the slaves, but whatever the case, there has to be some deeper reasoning for Roger's actions here. Like, maybe he was worried about destabilizing the world before the next generation had a chance to act, but then again, there's also a pretty fair motive in that the captain of the Rocks Pirates has now been said to be Roger's first and most formidable rival. So, you know, step aside, Garp and Whitebeard, because this world has a new profound legendary figure to fall over and to top it all off, his full name is Rocks D. Zebek. And at this point, we have an information overload happening. To be perfectly honest, when I first read this chapter, I completely forgot that Rocks was a D with everything else that we were bombarded with until I reread the chapter and went, oh, wait, holy crap, that was a thing. But you know what? I love this development because it looks like we have our second morally ambiguous D in the series. And here's how this whole thing is going to play out in YouTube theories for the next few years. Marshall D. Teach inherited the will of Rocks while Smunky D. Luffy inherited the will of Roger, thus making them destined to engage in climactic battle to once and for all settle this will of D situation and possibly select the next pirate king. And the thing that adds more evidence to that is that, well, the name Zebek is much more important than it may seem initially to anyone who doesn't keep up with the One Piece Viviocards, which I imagine is most people. But Blackbeard's entry is very intriguing because it mentions that his ship is called the Saber of Zebek, which at the time seemed random as hell, but you know, here we are. And when I should say thanks to the Library of Ohara for translating that Viviocard and allowing us to now make that connection. But I'll leave that one out there for the thirsty, thirsty theorists. But for now, I'm really enjoying what we've seen of Rox's design. He's quite notably much more normal size than the gigantic humanoid beings on his crew and he has a wonderfully intimidating smile. I absolutely cannot wait for the Rox to be expanded upon more and I'm certain that it will be done during the Wanoark because I believe that this is where the Rox will meet their final demise following the defeat of Big Mom and Kaido. But before that, there are some amazing names dropped incidentally that were also part of the Rox Pirate. Golden Lion Sheiky would be the most notable one and it's always nice to see him referenced in the canon material of One Piece because despite his film not being canon, he was a fairly important figure in this world so any background on him is always appreciated. The other name that stuck out to me was of course Captain John though. Now this man has been mentioned a fair few times in the series in relation to Buggy who has spent a long while searching for his treasure and not only that, but John also made an appearance as one of the corpses reanimated by Gekko Moria. Apparently John met with a classically pirate fate as well having been killed through multiple star bones for hoarding all the treasure for himself. So it's super interesting to see him linked to the Rox like this. But there's also two figures who we know sweet, sweet nothing about being the Silver Axe and Ochoku. So just as when the Rox name was first dropped I have no real comment on these two. It's just names one of which is confirmed to be based on a historical figure being Ochoku who allegedly draws inspiration from the Chinese pirate Wangzi. But other than that, it's nothing but names. However, it is entirely possible that one of them may refer to Miss Bucking who is 99% confirmed to have been a Rox member at this point because Marco did state that she and Whitebeard were once on the same pirate crew. So unless that was another unrelated crew Bucking was almost certainly a Rox pirate. And now Marines, Fujitora of the Marines. He was a great way to open the chapter and I'm fascinated because he appears to be quite injured presumably due to the skirmish between he, Ryokugu and Sabo and the Revolutionary Army commanders. However, I'm much more interested in where he is at the moment because despite conversing with Sakazuki there's a lot of commotion happening with cannons going off and Marines running everywhere with guns and stuff. So I think it would be pretty reasonable to assume that he's currently part of one of the four simultaneous missions to capture the former warlords. The one who I'm fairly certain it isn't his weevil because he looks to be fighting on land. However, the other three are very much up for grabs. The presence of the Seeking looking creature might lend evidence to the idea that Fujitora is in the calm belt and therefore Amazon Lily to deal with Boa Hancock. But then again, Marine vessels are supposed to be lined with sea stone so is not to encounter Seeking. So it could also be buggy or mehawk though. However, I'm not convinced that the Marines would have sent an admiral after buggy. All right, this video is it's going on forever, isn't it? But there is more to talk about. I mentioned it before, but a location named God Valley was brought up and shown this week in a beautiful panel by the way with the word God Valley and some stunning block font in the background behind it. But there's a hint of developing story here with the mystery of how after the incident with the rocks it disappeared without a trace and that the world government went out of its way to erase it from all knowledge of the world. So an easy technical explanation for this would be the Busticle, but I love this. It's one of the things that had my mind going the most insane during the chapter because I just need to know what was so important about that island that it had to be eliminated from existence. And I'm sure that it was not just the fact that it was battleground because the celestial dragons were there and everything it is almost certainly of the utmost importance to the true history of the world. Once again, theorists can tackle that one. And finally, because this chapter was not insane enough apparently, the very last note we're left on begins to connect us back to Wano with some discussion of Kozuki Oden and his affiliation to the grand figures of the world. And there's a very specific reference made to Wano perhaps being a bit too coincidental to be the central location for so many profound individuals. It was very cool to hear that Oden used to be a division commander of the Whitebeard Pirates though because I'd actually forgotten a little tidbit that he did sail on their ship and it makes sense for him to be powerful enough to have risen to that position. But in general, for a man who exists purely as a silhouette at the moment, Oden seems to be an incredibly key figure in the events of One Piece as a whole which makes me incredibly excited to see his eventual story. And I'm hoping that making the last page of the chapter more Wano-centric is the beginning of our transition back into Act 3 because we have got a lot of business to tackle and I'm keen to get straight into it. But that pretty much does it for chapter 957. If you enjoyed this video and the content this channel produced in general then please do consider donating to the Grand Line review Patreon because the support of all of your amazing people is what continues to make this channel possible. And if you'd like to see more videos like this but applied to other anime manga series then please do check out my second channel New World Review for all of your wider needs. And if you'd like to join the fun at any time then please do head over to my Discord server where you'll find a wide array of shenanigan retakes placed on a daily basis. And finally, please do comment with your thoughts on the chapter. This has been the Grand Line review and I'll see you next time.


Hey guys, it's Nate, AKA The Foot Accountant. Welcome back to the channel. We're taking a look at the guaranteed team season SBC today, the EFL SBC that came out. I want to talk about that and the market movements that we saw because of it. And stuff that we're actually seeing tonight, early morning UK time as I'm on foot right now and loading up this game. But this SBC did cause some movements today. I want to talk about it and also kind of share what I think this means for the upcoming days and the community team that he's guaranteed. And what I think EA really has a different agenda this year with team in the season and what they're trying to do with these cards because they learned from last year, especially from a trader sense and a trader standpoint. It seems like they're really trying to limit the amount of rise on these cards out of packs in my opinion, cause they're centralizing so much at the end of the whole entire week. So that is a very interesting thing that I think I want to talk about a little bit today. But of course the EFL SBC, I did mine and I got the Welsh left back. I think his name is, what is his name? Jacobson, I got Jacobson from my pack. So I got a 85 rated Tots card, a 15,000 coin card and I turned in a squad that was like worth 13K. So basically it broke even. I got an SBC card that I'll be able to turn in for a SBC down the line. And that's the thing with a lot of these cards that basically if you did this SBC today, you basically picked up on an SBC fodder card. Most of you guys did accept if you got Mateus Perea, Phillips, Mitrovich or Benorama. Some of these other guys are usable, right? Like Ajai in this EK way are pretty, they're usable, even Williams, but they're just not like amazing, right? And a lot of guys are asking me if you can invest in these. Let me talk about this as well. The whole point of the SBC today, this is how I view it. The whole point of this SBC that was put out today of the guaranteed EFL Tots, the reason why EA made it so cheap. It says 20K, but you can do it for less than that. The reason why they made it this cheap is to just supply people's clubs with team of the season cards. Basically what they did today was they gave people a free team of the season card. People knew they weren't gonna get anything sick from this, right? But everybody still did it because you had a chance to get Phillips Perea, one of these high rated guys. And even if you get an 87 or an 86 or above, you technically made your money back. Even an 85, you could say that you made your money back because the SBC was so cheap. So they just reeled everybody in today with that because it was cheap. And the reason they do that is to stop the rise on these cards when they're required again for SBCs in the future. So they're supplying them out on the market. Example, another example of this is EA making these cards available in packs for 10 days. They're gonna be in packs during lighting rounds again this weekend. So even more supply for these cards because EA is undoubtedly gonna be requiring them down the line for more TOTS guaranteed SBCs. They're gonna require you to put in a team of the season. So the longer they keep cards like this in packs, the discard ones, the more they supply them, the less the price out of packs because EA doesn't want traders to make coins. And that's, you know, they haven't actually come out and said that. But a lot of this stuff they do on this market points in that direction, which is also what I think they're trying to do with this team of the season, the community TOTS cards as well. Which right now, these cards in the last like hour or so, I don't know what the reason is right now on the market. I was just live streaming and we were looking at a lot of these cards. PK was 225 an hour ago. He was just 200K flat. Casimir was just 110K. Tiago was 110K. Brandt is 169,000 coins right now. These prices might not be the same when you guys are watching this. You know, it's a couple hours after I'm recording, but I mean, look at this man, Brandt literally a couple hours ago was 180K and he's down in the 160s. I don't know why there is panic selling on these cards. I don't know if people think the guaranteed TOTS SBC is coming today on Wednesday. But my feeling is that it is definitely not, especially since we got the guaranteed EFL today. I feel like it's not a guaranteed, but I think the highest percentage chance for us to get the guaranteed community SBC is gonna be on Thursday. And it makes sense for EA and what they're trying to do with the market as well. If they put that SBC out on Thursday, what you have is you have rewards supply from people opening their rewards and people opening their elite packs and getting these cards tradable in them. You have all that supply coming on Thursday from rewards. Then you have this pack SBC that comes out then and you have maybe people that are panic selling because they think they're gonna pack one of these cards from that SBC. So you have a ton of supply on that day. Maybe you cause some people to lose coins, but then the next day, you have a whole other team this season coming out. So then people maybe don't buy up these community team this season as much just because they're all hyped up for the next day and what's coming out with the next set of team this season cards. I really think that's why EA is making this SBC when it actually is. And PK is right now basically back up to 216,000 coins. So these cards fluctuate a ton. As we can tell, they fluctuate a lot on the market. But yeah, that's why I think they're setting up the SBCs this way. And a lot of people said, yo, Nate, the SBC has to come today because if they put it out, it's gonna be available or it's not going to expire before Prem Tots starts. I don't think that's a problem for EA. I don't think they mind that overlapping that SBC a little bit because ultimately if they put that SBC out on Thursday, right? Guaranteed Tots community on Thursday. 90% of the people that are gonna do that SBC in the entire world, I would have to imagine are gonna do it on Thursday within that, you know, even 12 hour period, the first 12 hours on Thursday, that's probably what they're gonna do, right? So that's why I see them doing this SBC on Thursday to try to stop some of the rise from these cards. Now it could backfire on them. It could absolutely backfire on them because I think a lot of these Tots cards have the potential, even though they're supplied a lot, they're so freaking cheap that I think they're going to rise out of packs decently nice. And I think Thursday is now definitely our time to buy these cards. The time specifically is going to be, is it gonna be earlier on in the day? Is it gonna be later on in the day? That's gonna be tough to decipher because weakening rewards come out before 6 p.m. UK. So you might have an influx of supply, but then you might have a little bit of panic selling because of the guaranteed SBC coming out. But I think Thursday is gonna be our time for these cards and a lot of them, the really meta ones that are, you know, more end game and just more hyped up that, you know, maybe are more rare in their position with their league and nationality will rise up pretty quickly into Friday. You might see a bit of a bounce there and then continue going up because Prem Tots cards are not gonna be anywhere near close to these low prices, man. Fernandinho, 223K, not bad at all. Fernandinho on Friday, if it is Prem Tots, which it should be. This Fernandinho card literally might be one of the three cheapest Premier League cards or four cheapest Premier League cards on Friday when Premier League Tots comes out. It seriously might be one of the cheapest, maybe even one of the cheapest three because last year we had everybody was above like 300,000 coins on day one, I think, except for Madison and Doherty. And yeah, so I think that Fernandinho is gonna be a nice riser out of packs as a Premier League card. Even Zaha, Felipe Anderson should do well. Brandt has some hype. Thiago has some hype. Piquet and Castamiro. I think a lot of these guys have potential to do good. By the way, if you have not bought a Courtois or a Bousquets and put it in your club, you should right now go out and do that because a 93 rated card for 83,000 coins. The 90 rated Obloc got over 100K. The last time we had an Icon SBC. So, you know, picking yourself up a Courtois, I think I have a couple of Bousquets on my transfer list. I did this last year. Bousquets ended up going to 130K at one point during team this season because of upgrade SBCs that were put out. So if you can get this guy as close to 80K as possible, I think he's even cheaper on the Xbox like 75K on the Xbox. That's just an incredible buy in my opinion. 86K for Bousquets right now on the PlayStation and Courtois is under 90 as well. Basically, if you get any of these guys like under 90K, I think it's a really good deal because these guys are gonna become SBC fodder really, really quick. And I think I might pick myself up another Courtois here if I can. That said, 91K. I'd like to get it under 90 if possible. But Thursday is gonna be a great time to pick these up as well because the fodder guys could go a bit lower before 6 p.m. UK with the new guaranteed SBC. To me, boys, I think the guaranteed SBC is gonna be on Thursday. That's my thought with it. So we're really gonna wanna watch these cards on Thursday and see if they do rebound a bunch, which I think the possibility is there. And the possibility for them to rebound even more is gonna happen later in the weekend or maybe after the first couple of hours of lighting and then they're gonna rebound. Lightning rounds coming on to FIFA on Friday for Prem Tots just because people are gonna see that the new Prem cards are so overpriced and they are just very, very expensive. So some of these guys, as they hit a low point, if you want them for your team or if you wanna buy a couple for an out-of-packs investment, I think the opportunity is absolutely there. Just pick the meta ones, right? Lucas Lleva, eh, not that really, not meta. You know, Gulashi is an SBC investment, 40K for an 89 rated. Musa Dembele is an 89 rated French striker that a lot of casuals can go out and buy this card.  I think people will play this card with Ben Yetter. You know, we have Baki Oko that was out recently. So this guy, if you see him under 80K, that's disgraceful in my opinion. This is also, this is already a disgraceful price in my eyes, 84K for this card.  I mean, you look at the guy's card stats as well. He's got four star skills. He's got 94 finishing, good agility and balance. You throw a Kemp style on there. You may be a sniper or something like that. And it's a crazy looking card. So I know he's got a three star weak foot, but that's gonna be a card that I think rises out of packs really well as well. So yeah, just watch some of those prices on Thursday. Even today, if by some reason they dropped the guaranteed SPC today, I think that might be our time to get in. We'll see based on the amount of panic, I'll be tweeting out my thoughts on that if that does happen, of course, on Twitter. But let's look at the fodder side of things. Because again, I wanna talk to you guys about SPC fodder and what happened today with the EFL Tots. Cause I know a lot of you guys have SPC fodder right now. You don't know what to do with it. Should I hold it? I'll buy more. This is what happened today, right? I think a word got out that the SPC was gonna drop and look what happened. He massive, massive rise. People thought that since it didn't come on Monday, then it was surely gonna happen on Tuesday and it did. And 84, the index 84 actually hit its peak an hour and a half before the SPC came out. The SPC came out at 6 p.m. and 84s actually dropped. I think because it didn't make it 84 rated, that's partially why you saw the drop. So, you know, 84s aren't like that low. They're still a bit inflated. 83s though, they did go up a bit, although they were inflated. A lot of people bought. Look at this, man. This buying starts early in the morning, UK time. Rises up all day to the content drop time. It's a peak three hours after content drop and then it's kind of stayed there. But that's cause it's an 83 rated squad and you need the 83s. Now I wanna look at a different market index. This is the index 86, all right? Look at this drop in price. 86s are almost viable again. 16,000 coins, 17k for some of these 86s if you did not get in the first time, which was me. This is, I did not get in the first time. This is not terrible prices for what we're seeing right now in foot. I don't know what the whole, I don't know why these cards are still maintaining a 17, 18k price for the 86s or like a 25k for the 87s. I feel like this should be 15k right now and 20k. I don't know what the deal is. There's not a ton of great SBCs out. Just some okay ones. But I think that just there's so many people on the game and the SBCs are what they can do right now. That's why you're seeing these cards. So if you wanna get in on fodder, if you hadn't already or if you wanna buy more, this is an opportunity for you to do that. Since they mean these guys were 20k this morning. Now they're down to 17, 18k again. So I would just do a club stock if you haven't done anything, I probably should go out and try to get these guys on bid at 16k. Some of these at 23, 24 and just do a club stock because there's gonna be a point where this stuff just goes high again. We're gonna get the community tots. Everybody's gonna wanna do that. That SBC should be around 86 or 87 rated at the most. So you might see the 86s go up a decent amount then. But there's also a lot of investing on these cards, man. There's a lot of people that are unassigned that have bought up on these cards. So if you have people that are investing a lot, that's why I only wanna do myself a club stock. I don't wanna get caught up in buying cards and trying to invest and being unassigned.  It is a guaranteed SBC. But again, we're talking about guaranteed SBC for community team this season, not that hype of cards. So people are still gonna do it yes and the price are gonna rise. But you're also gonna have a lot of people that are going out in panic selling. Some of those investments. So I would just do a club stock at most. If you're gonna go harder on anything in terms of a investment, I'm still shouting this guy out because he went even cheaper today. Fubin was saying he was 24K at one point. Anything under 26 in the 25s? Wow, he's under 25K. That's where I guess you have to buy this card right now. That's crazy, man. 25K or below for Javier Martinez is fantastic in my opinion. Parisich as well, I think is around a similar price. I'm just gonna start by Cam. I think I have one of these in the club. I might not though. These guys are both fantastic investments out of packs because of their links. Okay, Parisich is a little bit more 27K but I don't mind that at all just because of their links that they get and this is a usable card, man. And he's currently SPC fodder which is pretty crazy to me. So if you wanna pick up some fodder, 86s and above be my guest for a club stock only. If you have 83s or 84s and you haven't sold yet, 83s are all 2,000 coins even like the really cheap goalkeepers. Like I think Jarstein is up here at 2,000 coins. This is an absolute sell time for those just because when Jarstein is 2K, you sell because usually he's 1K and you're doubling your coins if you invested in him. So I would sell the 83s to be honest. 82s, the cheapest 82s in this game are even 1,000 coins apiece. So, I mean, if you bought 82s at like 750, look at this. Like 1,000 coins, 1.1K. So I would even cash out on those because you wanna be cashed out on a lot of the fodder because by Friday when we have lighting rounds again, the fodder is gonna dip low again. It's prem team this season. More saved up packs are gonna be opened in the first hour of lighting rounds for sure on Friday. And we're gonna have a similar situation to maybe what we had last Friday with some stuff getting low first hour in terms of like fodder, in terms of maybe some of the Tots cards. And we'll talk more about that later this week about some of that. But the last thing that I want to leave you guys with is just the fact about rewards this week. I don't know if I'm gonna do Thursday flips. I don't know if I'm gonna do Thursday flips. I'm gonna save a lot of my coins for Friday. And I wanna just kinda sneak peek this and talk about this a little bit and just kinda get this in your guys' minds. I think that a lot of the panic on Friday, there's so many people that are gonna be sitting down ready to invest on cards on Friday morning. So I think that you guys actually wanna be looking at stuff Thursday night. Last week, some cards were actually their cheapest during early morning hours UK, like nighttime US because there was nobody buying them. Everybody wakes up on Friday and is like, okay, this is our time to buy because cards are getting panic sold. Some of the cheapest points for some cards, not all. Some cards was last week, but so many people and even myself have been talking about the Friday morning panic for this Friday in foot. I would just be careful with that because there's gonna be a lot of people that are looking to buy panic sold cards. You're gonna have a lot of competition on buying. So honestly, getting in early or if you think there's gonna be more panic after they drop the prem tots, Friday morning is just gonna be an interesting time for out of pack special cards.  So that's my little tidbit on that. We'll talk about more of that this week as well. Also, last minute thing on this week's team of the week, I think these higher tier guys are actually decently investable. Let me get back here to all the way to team of the moments 32. We are not getting another team of the week, although supposedly on foot bin, there's this informed Fabian that is showing up in the code right now. I don't know if that's supposed to be an SBC today. We'll find out at 6 p.m. But we're not getting a team of the week today on Wednesday. And I think some of these guys are pretty low when they're being overlooked. Kula Bali, Firmino, Benzema, Griezmann, and Abomian. This Abomian card is actually really rare. Look at the supply on this Abomian on PS4, bro. On PS4, this man has how many pages of supply? Seven pages as an inform that went through lightning rounds last week. This guy at 300K or below, I would like to say 290, but he's probably not gonna be, he's probably not gonna stay that much higher soon because he's going out of packs. It may be a little bit of panic selling Thursday night and to Friday as well. I think that's a really good pickup. Prem Strikers are always, always heavily desired in this game, and if he gets a tots, that's gonna bring attention to his name. So that's a nice market talk video today, talking about the guaranteed SBCs and what could be happening these next few days with fodder and everything like that. If you enjoyed it, smash your thumbs up on it. Comment down below if you have questions and subscribe to the channel. If you're new, it's been Nate, the Foot Accountant. Catch you guys later. Peace out.