Yo, hey, yo, hey, yo, yo, pack the chrome styles, fly like Mrs. Jones. Lyrical mathematics will have the devil smoking stones. I put heads to bed, lick shots and rap with flit, with a church of food times. Obviously, we're hyper-coms wrong. And then they sell this at the CS department there. So I got one. All right, so let's jump into this. Hopefully, everybody had a good break. So quickly, on the docket for everyone, if you haven't signed up for a system for Project 2, please do that. There was a couple. I added some new suggestions. There was another crazy database that I've added from somebody else. If you want to write about that one. And if you haven't approved it yet, please poke me in it, and we'll go ahead and do that. And then for Project 3, the next thing coming up for you is the status update for April 3. There's some groups I still need to follow up on about additional feedback. And then the final presentation we finally have a date and time for the final exam will be on May 5, at 5.30 PM. I guess we can just do it in here, I think. And then we'll get pizza, OK? Any questions about Project 2 or Project 3? And then when is in Brunei for the next month or so? I think dealing with visa issues and the s**t there. So let's get back into the world of databases. So today, we're going to talk about sort merge joins. And the main spoiler is going to be that this is almost always going to be inferior to hash joins. And some systems won't actually implement sort merge joins. The enterprise systems will implement this, because they won't implement both. Because there's some cases where you want the output to be sorted on the key that you're joining on. So therefore, sort merge join would be better, because you can kill two birds with one stone. But we'll go through what it looks like and how to speed it up. And again, it applies some of the techniques that we've talked about before of how to use SIMD, how to run on multiple cores, how to be aware of the number of regions to get the best performance. And the reason why I'm still including this, because when we start talking about worst case optimal joins or the multi-way joins on Wednesday, they're basically going to be doing a sort merge join, but with more than two tables at a time. So for the sort merge join, the high level idea is that we have two tables we want to join, R and S. And we're first going to sort them on the keys that they want to join. Then once that's done, we would then have this merge phase where there will be two iterators pointing at each of the two tables. And the idea is that we're going to walk through in lockstep and compare against whatever the two iterators are pointing at. The idea is that because we've sorted things ahead of time, that the iterator, at least on the outer relation, will never have to backtrack if we're doing an equa join without duplicates. We'll never have to backtrack, because we know that the thing that we're looking for cannot be above where the iterator is actually pointing at. If you're doing a naive nested loop join, you're doing sequential scan because you're looking for a needle in the haystack every single time for every single tuple in the outer relation. You're doing a complete scan on the inner relation. But with the sort merge join, because we pay this upfront cost of sorting the data, then we don't have to worry about it. We know that the thing that we're looking for can't be some part of the table that we've already scanned. In the very beginning, I'm not going to just define the sorting algorithm that's going to be. That's the main thing we're going to focus on. We'll start focusing on that. In general, I would say the modern variations of quicksort are going to be the vectorized ones, or going to be what you want to want to use. For really large relations, for smaller ones, we can use the batonic sorting and networks that we'll talk about in a second. Visually, it just looks like this. We have two relations. We're going to sort them. I'm not defining what the sorting algorithm is just yet. Then we do a merge in the second phase. Again, we have this iterator, which just rips through, and it's comparing against a tuple from the outer table or in a tuple with the inner table. What's going to be confusing today is that we're going to be talking about the sort merge join algorithm, but then the sorting algorithm we're going to focus on is a merge sort algorithm. I'll just try to be careful when I talk about the use term merge, where I'm talking about the merge phase of the merge sort or the sort merge. Which can be confusing. Again, there are vectorized variants of quicksort that we can use for this as well. We talked about sort merge joins in the intro database class, 445, 645. But we didn't spend time talking about workers or numeric regions or vectorization. So what this lecture is really about is how to now consider these other design decisions to get the best performance on a modern CPU, on a modern architecture. So in a parallel server's join, the sorting is obviously going to be always the most expensive part. The way we're going to speed up sorting is through parallelization. The idea is we want to execute the sorting across multiple threads, multiple workers, and then have each of those workers try to use vectorized instructions as much as possible. And then the merge phase is then to figure out, okay, where are we going to put our output of our sorted data and how are we going to do our comparisons? Is it going to be local in a memory region or a numeric region? Or are we going to allow them to go communicate to any possible core? And then we trade-offs for doing what the reads writes in the beginning or what the reads later on. There's no free lunch and we'll see how the different approaches to do this. So the main rules that we're going to try to do here is we want to use as many CPU cores as possible. Now, in some database systems, they're only going to execute one query at a time. So in that case, you just get all the cores that are available. If the system's trying to multiplex queries and have schedulers, then you have to decide how many cores the query can use. But we can ignore that for now. And in a modern CPU architecture, we care about numeric regions because again, the remote memory access, if you want a different cores on a different accessing memory region that's local to another CPU socket, you're going to put a 2x latency penalty for that. So ideally, you want to access the data that's local to you. And ideally, only access data that's in your CPU cache. Or if you bring something to the CPU cache, do all the work you need to do on it before you move on to the next data item. And of course, we want to use vectorize same-day instructions when possible, all right? So in the case of the hyper approach, they're going to ignore this, the second one here, the NUMA boundaries. Because they're going to make this claim that, oh, that all the threads that when you're doing the merge are going to be doing sequential reads. And therefore, it doesn't matter whether the data's local or not in your local NUMA region because the Harbor Prefecture is going to hide all that for you. And have you shaken your head? No, right, in the paper you guys read, it turns out to be not the case. I should have asked Thomas why you believe this back in 2011. I would say in the new version, Umbra or the new system they're building, they don't even do sort merge join. They only support hash joins. So just like in the parallel hash join, in the parallel sort merge join, there's going to be three phases. The first phase is the partition phase where you can split the data up using the radix partition parts that we talked before to divide the data up across different cores and different workers based on the join keys. This is the same technique that we talked about in the hash join before the class, before the spring break. So we don't need to discuss this because we know how to do that. The idea is basically the same thing. Then in the next phase, again, we do our sort based on the join keys and then just look forward, we do our merge compare tuples. And then depending on whether we're doing echo join or not, depending whether it's duplicates or not, we would only need to scan the outer table once. The inner table, you have to backtrack. So you have to keep track of where you left off or where the last unique value looked at and jump back to it, okay? All right, so for the sort phase, in general, the quick sort is probably going to be what most systems are going to use. And in the intro class, when we talked about the sorting algorithms, we always said that the disk was the main bottleneck. And that the reason why we couldn't use quick sort is because we were concerned about spilling the disks when we do our pivots and splits. And therefore, we chose to use the extroversal algorithm because that maximized the amount of sequential reads and writes we were doing. But for this class, we're going to assume that our dataset that we want to join is going to fit in memory, right? And therefore, quick sort might be actually a good idea. And again, in most cases, it actually will be. So we're going to look at merge sort because again, it'll have building blocks like the sorting networks that will be used as the fast pass or fast, as an optimized shortcut, what's the word I'm looking for? Use case for quick sort, like if you actually look at the source code of the modern vectorized quick sort algorithms, they check to see if the number of keys is less than 128 or 256. And if so, then they're using the sorting networks that we'll use for the merge sort algorithm from Intel, right? The downside of this though is going to require an average and additional storage for intermediate results because you have to sort it and then take the input in and then write it out as a sorted array. So you need double the space. So we'll talk about the merge sort algorithm first because again, this will make it clear about the numeric regions and the parallel cores. And then we'll briefly talk about the quick sort, like the Google and the Intel one, which just came out last year. But there's another one, DGB sort, which I don't fully understand myself, but that claims to be the fastest one out of all these. So the sorting algorithm we're going to talk about is going to be a cash-conscious approach. And that just means that the algorithm itself has to be aware of what the hardware it is that it's actually running on, the size of the CPU caches, the size of the registers, the number of registers it has to deal with.  And then based on the size of the data that we're trying to sort, because we're going to sort of do a divide and conquer approach, that we're going to switch the strategy as the sorted runs get larger and larger. So this is the cash-conscious sorting approach from Intel from 2009. Remember when I talked about the different, the history of like the sort merge join versus hash join algorithms. I talked about how there was this paper in 2009 from Intel and Oracle that said, hey, we have a hash join is currently faster, but if you had a 500, AVX or 512 bit Zimni registers, you could do sort merge and that could be faster. So that's what this approach I'm describing here is what they proposed back in the day. And only now with AVX 512 can we actually implement this. So you get the idea here is again, we're going to break up our total dataset we want to sort into smaller, disjoint chunks called runs. And we're going to sort those runs and then we're going to then merge the sorted runs of the same size into larger sort of runs. The idea of we progressively get bigger and bigger with these sort of runs until we end up with the entire table sorted. And again, the idea is that we're going to have these different levels and use these different approaches based on the size of the run that we're sorting. So in level one, we can have things fit in our CP registers. Could be Zimni, it could be the CP itself with the SISTI registers. And we're going to try to do, or we will do sorting based on that. Then as it gets a little bit larger and we exceed our registers, we'll switch to level two or do in-cache sorting where everything fits into the CPU cache. And then once we exceed that, the last level cache, then we'll switch to the out-of-cache sorting. And then the challenge is going to be that we're always going to need for each level because we're doing, we're not doing in-place updates to our data as we sort it. We're always going to need double the amount of space in each level for our input data. So in level two, you can only sort a run that is half the size of your CPU cache because you need to use the other half to write the output. So we're not going to worry exactly about the difference between L1 and L2 and L3. As long as it fits in our last level cache, the L3 cache, and not spilled a DRAM, then you can choose to do this one or the first one. So basically, I think a modern CPU is now like Intel, the 2022 Sapphire Rapids. That's like, I think 90 megabytes of L3 cache. That AMD, the Ryzen 9 is like 128 megabytes. So it's big, but not like, if your table is gigabytes, then this won't work. All right? Oh, sorry, you can't do everything in level two. I'll say also that I'm calling this level one, level two, level three. This is my term in the original paper. I think they call it phase one, phase two, phase three. But if we're talking about sort merge join, which has the sort phase and the merge phase, then there's too many phases and I just call it level, right? Okay. So again, pictographically, it just looks like this. So we have our unsorted input data, either from the outer relation or the inner relation. And then in level one, we're going to break this up into four element runs. We're going to sort that. Then we go to level two. We'll now have sort of runs that are half the size of the CPU cache. And then when this exceeds our CPU cache, then we go down and do to level three and we keep doing, you know, keep combining things together until we end up with our entire data set sorted. Right? So again, that high level just get divided and conquered and we're going to merge things back together. So level one is going to use what's a technique called sorting networks. So this is an old idea that goes back to the 1940s. But back then it was described in terms of like hardware. So they're going to refer to things as like wires, like you're going to compare the values on wires and write it out to a wire. They literally meant back in the day, like writing the actual value on a copper wire because it was 1940s. But now obviously everything's transistors and we're going to do this in software, right? So the idea is that you have these four inputs coming in and then you have these comparator steps where you're going to compute the min and max for the values that are coming on these two wires. And the min will get written out to the top wire and the max will get written out to the bottom wire. All right? So in the first step here, these values get passed forward. So here we're going to do a comparison between nine and five. Five is smaller than nine. So it gets written out to the top wire. Nine is larger than five. So it gets written out to the bottom wire. All right? And then same thing down here. Three and six gets written out. And then this gets passed over now. You take the output of this wire, for this comparator, which is five, take the output of this wire, which from this comparator, which is three, and you compare five and three. Three gets written to the top, which goes fed out to the output array and then five gets passed over to the next one. All right? And you keep going on like this. Then you end up with, that's finally your sort of the array, right? So in our example, we're using four elements because we're doing, we can assume we're going to do 128-bit SIMD registers, which you can do four elements in an AVX 512, right? Because you're going to need assuming 64 bits for the key and then 64 bits for the tuple ID. Again, this is why, again, the original paper from 2009 said, hey, if we have AVX 512, we can do this because if you need 64 bit keys and 64 bit IDs, you need 120 bit lanes. In the Columbia paper, where we talked about the different approaches to do SIMD algorithms, they only had AVX 2, which is 256 bits, SIMD registers, but they were assuming you had a 32 bit key and a 32 bit tuple ID, right? But again, in a real system, that's not the case. You might have 32 bit IDs, or sorry, 32 bit join key to compare against, but most systems would use 64 bit pointers just to keep track of tuple IDs, right? So this is kind of neat. Why would this be fast? What's the sort of obvious about this? Think of it quicksort, quicksort, what do you do? You find a pivot, and then you can start to compare two things, that if one is greater than the other one, you put it here, the other one's greater than the other one, you put it there. It's branching, right? In this case, there are no branches. It literally is, take these two inputs, the main goes here and the max goes there and it just gets written out. You just do these straight Minimax instructions. So that means that the CPU is always gonna execute the exact same code to do these comparisons no matter what the input is, and then you end up with the sorted output, right? So the pseudo code for this is like this. So you have your original keys come in here, right?  Then you do the next round of comparisons, this one here, that gets written out to the output, same with the bottom one here, is that correct? No, that looks like wrong, that should be something else. But anyway, like these instructions are the same, no matter what the input is, and there's no branching, right? So again, this is fantastic, but of course, this doesn't help us if we have, that the table has four tuples, and we don't need to do any of this sort of virtual stuff. But again, we can use this as the building blocks to create the small runs in the beginning, and then get larger and larger sizes. This question is, if you're doing this in the same day, how many instructions do you have to do this? Next slide, okay, thank you. All right, so the first thing I'm getting to point out is I'm showing just single key values, but in actual reality, this would be, again, it's sort of a 64-bit join key and a 64-bit pointer, right? So you think about it, even though it's gonna be a 128-bit number, when you do your comparison, you really only care about the first 64 bits, because that's the join key you're checking. So it doesn't matter that the tuple pointer might be greater for the tuple that has a smaller join key, you're never gonna actually get to that comparison because you're only comparing the first part here. And if two join keys are the same, then this is just a tie-breaker if you use a 64-bit pointer. But again, to make it fit on PowerPoint, I'm not showing that. All right, so this question is, how many instructions are we gonna use? So first, you need four load instructions to bring this all in, but now we're gonna do, we're not gonna sort within the single register. So think of each of these arrays, think of that as the register. We're actually gonna be sorting across the registers. So think of this column here. So we're not gonna sort 12, 21, 14, and 13. We're gonna sort down 21, 8, 14, and 11, right? So now to put this in our sort network, to do the min and max across the column, we even need 10 min and max instructions for all the columns. And then we wanna do the, but then we need to write it out back to memory. But obviously, if we're writing contiguously within the register, then this is not sort of horizontally. We wanna do a transpose to convert it from column store to a rows store. So for this one, we just do a shuffle and then a store to put it, to basically pivot it and flip it and then we can write it out, right? So this is what? This is eight plus 12, 22, and then 26 instructions. Versus like, again, it's 16 keys or 16 elements, but you would actually way more instructions to do this in a quick sort. Yes? So if we're trying to sort, sorry, if we're trying to sort. If we want to sort. Yes. So why don't we sort within the row itself? You can't do that, because you can't do comparisons for elements in the same lane, right? You have to do the vertical operations. So again, this can work because the sorting network is deterministic. There are no if clauses. It's always gonna be min, max, min, max, and so forth, right? And always writing up to the same location because you can't have SIMD instructions with if clauses because that constant doesn't really make any sense. All right, so that'll get us all the, that'll get us a bunch of runs for our input that are within four elements, right? But now we need to merge those together. So this is gonna be a technique called the Big Tonic Merge Network. And this will be similar to the sorting network, but the idea here is that we're going to merge two locally sorted lists with four elements into a do not globally sorted list. And it's gonna work the same way as before, but now again, we're just gonna shift things around so that it produces output that are now globally sorted. And we keep expanding the size of our network and it's gonna have the same property where it's deterministic, there's no if clauses. We keep expanding the size of the network until we get to that half the last level cache up about. So these numbers are dated. This obviously goes back to, well this is from an early paper. This is from Intel, this is 2008. This is how to do the sorting efficiently with SIMD and then they use that in the 2009 paper to say how do you sort where it's joined in the database. All right, so then they claim back in the day that you can get up to 3.5x faster over a system implementation. I don't know what the newest numbers are today. Again, I think the DJB sort claims to be the fastest one. I don't know whether there's a, anybody's keeping track of these things. So, right, so for this one again, we're gonna target L3 cache, which again, are gonna be, say 100 megabytes. So we can, so I mean the size of the sort of run we can go up to is up to be 50 megabytes. And then once we go beyond that, then we drop down to level three. So the idea looks like this. So say we wanna take two sort of runs from the last, from level one. So we're gonna store them in SIMD registers, but we're gonna put the first order run is gonna be in the sort order lowest to highest that it came out of, the first phase. But in the second sort of run here, we're gonna just reverse it. So it's gonna be highest to lowest. And the idea here is that when we start doing our Minimax comparisons here, because we know that this is the lowest for the sort of run, and this is the highest for the sort of run. Again, it's sort of a way again, for us to implicitly say the boundary within the values that we're looking at as we compare a sort of run. So we know there's not gonna be something greater or less than an element that I'm looking for. Because I know if I'm in this position for this input, I know that anything above me is less than me, and then below me is greater than me. And then same thing, right? If I'm sort of landing here, and I'm comparing with this, I know that depending on if I'm in the max, I don't need to go, there's not gonna be something else that I missed. I'm not gonna have a false negative. So you do this Minimax, you do this shuffle to move things around, and then you end up with your globally sort of run on the output like this, right? And again, it looks like the sorting network, just we have these extra shuffle steps because we're trying to do larger runs, right? And again, the great thing about, we can do this in SIMD is that we can do all of this with SIMD instructions with ABX 512. We don't need to write things back out the memory and then, or which would land in our cache, and then CPU cache, and then bring it back to the registers. We can do this entirely on the SIMD registers. All right, so now in level three, the idea is that we're going to continue using the bitonic merge networks, but now we're gonna split up the chunks that we wanna sort into sub tasks. And again, eventually as we sort the pieces, we'll just, we may need to write out some intermediate data, we'll have to stitch it back together. So Intel's gonna call this the multi-way merge sort, or the merging operation for level three. I will say that I don't know of any system that actually implements what they're gonna describe here. It's interesting to think about or look at, but as far as you can tell, nobody's actually doing this, because I think it's, they make unrealistic expectations on the bookkeeping overhead of figuring out whether the thing, the data you actually need is gonna be in the CPU cache or not. I'll try to meet what I mean in this second, but like, so I'm not gonna go through, here's exactly the code that they were executing. I wanna conceptually show what they're claiming that you can do. And again, you'll see as they're gonna try to always operate on data that's in the CPU cache, but they're gonna assume you have a way to figure out, okay, the next thing I need to do, that task is, the data that this task needs is resident in my CPU cache. So let me go ahead and execute it. But this other thing I need is in the CPU cache. So let me ignore that. And again, I think that's difficult to do because in the papers that describe this technique, they're test bed systems. They're not full-fledged database systems. So they assume that all the threads are just doing nothing but sorting data, which is not realistic, right? You'll be parsing queries, if you're running transactions, gotta do that.  And so being have that sort of precise control, like, okay, yeah, this thing is in my CPU cache or not, I don't think that's realistic for what they claim. So the idea is that if there's no work to be done for a given task, then they'll block it in the task queue, and then the CPUs aren't gonna be tied to a single task queue, they could jump around and say, okay, I'll start working here, I'll start working over there, right? All right, so it looks like this. So soon we have our sort of runs, and we need to combine them to the global sort of run. Right, so each of these sort of runs are gonna be fed into a merge task, which is then gonna be now producing some output that are gonna be cache line size chunks, I think, of work to be done. But in this case, right, and it keeps going, it does merge, so eventually we're gonna have our final output. Right, again, we know the size of the input because we've already scanned through it and sorted everything, so we know how many stages we're gonna have as we get these progressively larger chunk sizes. So again, this is all computed ahead of time. So the idea here is that we have these tasks that get put into a global task queue, but we're not gonna let any worker run on this because we know that we don't have all the input that we need to then start computing the merge in this phase here. So say we have some workers here running with some core, some thread, right, and it starts producing the output that's expected, and then once this, once it recognizes, okay, I've combined or merged all the pieces that I need to merge, then that trips this thing to be added to the queue as it is now being available. So soon we have a single thread, this side can again jump over here, it starts doing the merge for its input, that produces some output, then it maybe jumps back over here, does this, jumps over here, does that, and then it finally finishes up. Again, think of there's some global task queue that says is the work, is the input data I need ready for me right now and is it in my CPU cache? If yes, then the merge task can be run, if no, then it blocks. Yes. The question is, in this particular example, how do I take advantage of NUMA? I'm not showing that here, we'll see that in a second, all right? So again, I think this is conceptually interesting, this is a high level diagram, it's not showing where the NUMA boundaries are, I just think, as far as I know, nobody actually does this, right? So again, this is the Intel multi-way merge sort of algorithm, that will be the background of the, or the first phase of the multi-way sort of merge join that we'll cover in a second, and that was the multi-way sort of merge join that was evaluated in the paper you read about looking at all the different hash joins, that M-way bar in the measurement, that was using this approach, plus without partitioning, or with NUMA where partitioning we'll get to in the merge phase in a second. All right, so I'm gonna talk about quickly sort of other alternatives to doing the Intel merge sort. There's this other approach called the in-place super scalar sample sort, and this is actually what we used in our old system noise page when we were building that. So this is a generalization of quick sort that was developed in, well sample sort was developed in the early 1970s. It's a generalization of quick sort which I think came out in the 50s. And the idea here is that with quick sort since it only divides, every time you do a pivot it only picks the one pivot key and splits. The idea with sample sort is you wanna divide it to, you wanna look at the data and sort of try to do an educated guess or pick multiple pivot points. But in this implementation that came out, I think also from different Germans in 2017, instead of copying or instead of always having another buffer that's the same size as your input to write into, in this one they try to write the data back into their own input. So you don't have that extra storage overhead. And then for this one too is called super scalar sample sort is because they have designed it to be branchless as much as possible, right? When you do comparisons against different keys, right? Similar to like the sorting network that we talked about before. So this is another approach. This was claiming to be the fastest one in 2017. Fastest sorting algorithm in 2017. But in the last year we had a bunch of different implications of missing stuff here. That's fine. In June or July or early 2022, Google came out and said, hey, we had this thing called VQ sort written by the Swiss guy. And that's based on Google's highway library, which is their portable SIMD instruction library. So they're writing intrinsics. You could use Google's highway library to get SIMD because it supports different ISAs and different SIMD registers as I. So like AVX 512, AVX 2, so forth. So what's interesting about this, if you read the paper, they talk about how if you have less than 256 keys or 64 to 256 keys, they're gonna use the big tonic sort merge network that I talked about before, right? And then if it's larger than that, then they're gonna fall back to using a vectorized variant of a quick sort. And they claim to be about 1.59x faster than the in place superscalar support, superscalar sample sort from the last slide. So this came out in early in 2022, then Intel came out with their library in late 2022. This only supports AVX 512 on Intel CPUs for obvious reasons. And so they're gonna, they want to get people using AVX 512. So I think this thing exclusively supports it, whereas this one can fall back. And I think if they choose by default, they prefer using AVX 2 because you don't worry about that CPU or throttling issue, right? So the Intel one exclusively uses 512 as far as I know. Right? So again, there are better versions of a quick sort. And if I was building a system today, I would choose one of these or even the in place sample sort from the last slide rather than building this whole contraption of these multi-level thing that Intel does. Plus you don't have to write it yourself. And of course the Germans, they don't do any of this. They write their own Radex sorting themselves because that's what they do. And apparently they implemented, they took the, Thomas took the Radex sorting algorithm, quick sort that he wrote for Umbra or Hyper. Now that's in GCC for doing backchase or exception on rolling. So his code, the sample from Hyper, the sorting code of Hyper is now in GCC. He's on the standards committee for C++. I don't know how he has time to do this. It's insane. And that's why they make t-shirts there for him. Okay. All right, so we got past the sorting phase. Now I'm going to talk about the merge phase. Again, the idea of basic is we have the sorted out of relation, sort of interrelation. And we're going to iterate through them in lock step and compare the tuples that they're pointing at the joint keys. And then if there's duplicates, we may need to backtrack on the interrelation. Again, assuming we're doing equijoin, interjoin. And we just have to keep track of how to jump back to the starting point and a run of the duplicate values. So the database system is going to be able to execute this in this phase entirely in parallel without doing any synchronization if we're to maintain separate output buffers for every worker. We talked about this when we were doing the partitioning step in the hash join. You can have everyone write to a local buffer and then at the end you got to have everyone combine things and put it back together. You can do the sort of the radix partitioning trick where you know, since you know the number of values that each worker is going to work on, you could allocate space and say, okay, you could write this global buffer here and then you don't have to do any latching to protect those regions. So we can be careful of how we design the output location or the output buffers for our workers to avoid any sort of fine grained synchronization. And then that'll make everything run faster. So I will say also too, like the sorting algorithm that I just talked about, we can use those for things other than sort merge join. We can use it for order buys, we can use it for aggregations and then you obviously don't do any of this merge phase stuff. But again, since we're trying to talk about joins, we're going to do this. So I'm going to talk about three approaches. So the first two are going to come from the paper you guys are assigned reading from ETH, the M-way, the multi-way sort merge join, and then the multi-pass sort merge join. And then the last one here will be the massively parallel sort merge join. And this comes from Thomas and the hyper guys. Like I said, they came out in 2012, said sort merge join is faster than hash join even without AVEX 512. And then in 2013, they came out and said, we were wrong, hash join's better. So this is their approach that they said they cleaned was faster than hash join from back in the day. And then as the paper, again, we saw from last class, this is the sort merge join that the other Germans are going to compare against. Too many Germans. The other German paper is going to compare against and it's going to lose to hash joins. So let's go through each of these one by one. So in the first one, the multi-way sort merge join, this is going to be the best approach of the three. And it's basically everything that I've talked about so far with the cash conscious sorting up to level three. But then when during the merge, they're going to have each thread is only going to look at local data because you're going to partition it into different numeric regions. And then as you do the merge, you're only looking at data that's in your numeric region. You don't need to do any cross-traffic. So the idea is here, you pay this up front panel, you're up front cost of writing out the data in this partitioning step. After the data is sorted, then you write the sorted partitions in different locations. But then when you do the merge, you don't need to look everywhere. So visually it looks like this. So first we're going to do local numeric partitioning, where we first have the workers are only going to sort the data that's located to them. And then we're going to do this multi-way merge since we know the boundaries of the values that we've sorted that we're going to have each worker write to one other worker's numeric region to memory location with all their values within some range. And then, again, since the, as we do this write, since they're unsorted, as being written into, because we didn't do any global sort here. So now after they write everything to this numeric region, then they do the multi-way level sorting that we talked about before. So everybody's going to do the same thing. So now at this point here, we partition our data such that each worker has local data that is locally sorted. But globally, we know this is actually globally sorted as well, because we did the range partitioning as we wrote into this. So now on the interrelation, it's going to do the same steps as the interrelation, just because we're in PowerPoint, I don't need space, so I'll just say sort, but it's the same thing over here. So now at this point here, within each worker, it's going to have local data that is sorted. And that matches to the local data within the same boundaries on the interrelation. So now all I need to do is a local merge join within my worker, only the data that's in my partition, and then, again, just iterating through and comparison the tuples going across. So again, I had to do a bunch of writes here to put the data into different new regions, but now when I do the join, this is super fast, because everything is local, and there's no interference, there's no synchronization across different threads. The next approach is to do the multi-pass sort merge. And the idea here is that it's just like the multi-way sort merge, except that we're not going to do that redistribution. You can do the local L1, L2 sorting, like in the previous way, but now we're going to do multiple passes when we do our global sorting. And we do the same thing on the outer table. So again, it visually looks like this. So everybody's going to sort all their local data on both sides. Then now we've got to do this global merge join. So what's going to happen is every worker thread needs to look at every other worker threads locally sort of data on the inner side. So we're going to do one iterator pass on this data, plus one iterator pass on that data, and then do the same thing for the next one, and so forth, going down. And that's just one worker. We need to do this with everyone. So everyone's going to take multiple passes on all the data. Because again, you don't know if I'm looking at a tuple here, since this is not globally sorted, I've got to look everywhere. Whereas in the multi-way sort merge join, since I partitioned everything with range partitioning, so I know that the minimax boundaries for every chunk of data, I know that if I don't find a match when I scan my local partition, the key can't exist anywhere else. But in this approach, since I didn't do that global sort, I have to check everywhere. And so the hyperguys are going to claim that this is not a big deal, because the Harbor P-Fetcher is going to recognize that we're doing sequential scans and a bunch of strides of data, and bring things in ahead of time. So I don't pay the penalty for remote NUMA access. We didn't really talk about Harbor P-Fetching. We talked about Solver P-Fetching where you can pass instructions and say, I think I'm going to need this chunk of data and some range of memory, and the CPU will try to bring it into your CPU cache explicitly. It's not guaranteed to do it, but it's like a helpful hint. With Harbor P-Fetching, this is transparent to you as the database system. This is just saying, oh, I recognize you're ripping through some region of memory. And therefore, I know that you're very likely to continue to keep reading sequential data. So therefore, let me go ahead and pre-fetch the thing that I think you're going to need next. The OS does the same kind of stuff too for reading files from disk. Everybody's trying to pre-fetch avoid long stalls because Harbor is slow. Because CPU cache is going to be order mag too faster than going to DRAM. And double that if you're going to a remote region. So the idea is to use the pre-fetcher to hide all this. But again, in practice, it doesn't work out. So this is going to be the hyper approach. And they're going to do something that's much different than the two other ones that I showed. So the first thing I'm going to do is range partition on the outer table and then redistribute it to all the cores. And then they're going to sort the outer relation so that you have a globally sorted. The outer table is globally sorted. But you're not going to do the same thing for the inner table. So in the other two pressures, the inner table will do the same thing as the outer table. In this one, you're just going to do a local sort within each partition in the inner table. And the idea here is that now when the outer table starts iterating over the inner table in each partition, it knows the minimax boundaries that it's looking for. And therefore, it doesn't have to iterate, do a complete scan of the entire table. Once it knows that the thing I'm looking for cannot exceed this point on the inner table, I stop. So it looks like that. So again, we do our cross-never partitioning like before where we end up with a globally sorted data for the outer table. And then now, again, globally sorted. And then now for the inner relation, I don't redistribute. I just sort locally. And again, I can do any of the sort of algorithms that I talked about before. So now when I want to do my immerse join, again, I'm going to iterate through every two point of the outer table. But maybe I only need to iterate over a portion of what's in the inner table. And once I see a value that it exceeds the range that I'm looking at in the outer table, on the inner table, then I stop my scan. I'm going to do this for all of them. Of course, you have to do this for everybody. They're all doing the same thing at the same time. So again, this seems like a bad idea. It seems contradictory what I said before that we want to minimize the amount of non-local memory access. But again, they're going to claim that in this paper that because all your access, all your reads are sequential, that the Harvard pVetcher can mask all this, mask the latency. So in their other papers, they talk about these sort of rules for parallelization, which I think are kind of interesting we're discussing. I would say also, too, that these are valid, I think, also for hash join as well, not just for the server's join. Obviously, except for the sequential reads. So the first thing they're going to claim is that you want to have no random rights to non-local memory. You want to do all the rights. If you had a right to non-local memory, you want to do sequential writes. You just sort of bash those up and write them out all at once. For the rule number two is that when you have to read non-local data, you want to only do sequential access. Again, because the Harvard pVetcher is going to try to hide those memory stalls. Because it'll figure out that you're reading things that are contiguous. And of course, a solid rule for any algorithm is that you never want to have any worker or core way for another. So that means that they want to avoid the fine-grained latching or signalization barriers of, again, to be able to try to write to the same memory location, and you have to protect it with a latch. So if you can pre-allocate the regions that the different workers are going to write to for your output buffers or the intermediate results, then you avoid all that synchronization. And we saw that with the Radex partitioning stuff before, where you compute the prefix sum, and then you know the offset that everyone is allowed to write into. So I'm going to discuss the numbers from the ETH paper. And again, this paper is 10 years old. The harbor at the time is pretty beefy, although 512 gigs of RAM for a single node is still pretty good. That's why they have money. But I'm not so much care about the absolute numbers. It's more the comparisons of the different algorithms, and I understand why one might be better than another. And as I said, the spoiler is going to be the multi-way shortness join is going to be preferable to either the second approach or the hyper one. And then I'm going to compare against a Radex partitioning hash join. And again, as I said before, sometimes you're seeing a literature that say, oh, we're doing a Radex join without saying it's a Radex partitioned hash join. So the first graph here, they're comparing the three different approaches. And then what I like is that they broke down the runtime of the different algorithms based on whether it's doing the partitioning, the sorting, the sorting of the merge phase, or the join of the merge phase. And then again, what you see is that the multi-way one is just faster if you're measuring the number of cycles it takes to produce one output tuple. The hyper one is not even closer. And then if you want to measure in terms like the throughput, or higher is better, then you can see it clearly again in terms of how many tuples can I process as I'm executing things, the multi-way one is just faster. And it seems counterintuitive, because it's like the hash join sum. It seems like you'd be slower because you're spending more work to do this redistribution and do much of random writes to write out the data to different partitions. But because now when I do the merge operation and do the comparisons between the outer and the inner table, because things are all local, that makes me run faster. You sort of get things lined up the way they should be in such a way that it's the ideal way for how the CPU wants to run instructions and read data. So in terms of scaling up, again, this hardware is pretty old. In the very beginning, you see that if you don't have a lot of threads, there isn't that big of a difference. But as you scale up, the multi-way one is superior to the hyper one. So again, the extra instruction you're spending for the multi-way join ends up paying off. In terms of absolute numbers, it looks like this. Let's skip this. This is just looking at different sizes of the join tables. But again, you can see that in most cases, the hash join is always superior. I guess this is not even the best hash join. This is the radix hash join. And in terms of, as you scale up the number of tubals, again, the radix hash join was a smaller table size. It does much better, but then it eventually converges. And again, I think the overhead here is just you're spending so much time partitioning. And that's why the two approaches converge. So this is the graph that I showed before, obviously, without all those optimized results. Because again, I said nobody actually implements them. So again, this is the paper realized class. This is their implementation of the multi-way sort merge join, the first one that I talked about here. But if you're doing no partitioning on a linear probing hash table, you're going to get much, much better performance than this. And again, you can see why it might be misleading to think, oh, sort merge join would be better than hash join. If you say, oh, if I want to use hash join, I should be using radix partitioning, because it seems like it would make sense. And the literature claims that, at least prior to this, that this was superior. But simple is better. And it'll run much faster here. OK? So that's sort of a quick crash course on everything you need to know about sort merge join and sorting in modern systems. Again, the main takeaway is going to be that the hash join is always going to be superior choice. There are some systems that don't implement even the sort merge join, the only emulated hash join. Again, the German's new umbra system only does hash join. I looked at this before this lecture. As far as I can tell, most of the enterprise systems will do sort merge join as well as hash join. And it's up to the optimizer's job to figure out which one it should pick. Sometimes they'll call it a merge join, but the basic idea is the same thing. Now, we didn't consider the case where, again, if there's an order by clause in your query or you're doing an aggregation right after the join and therefore you can rely on the sort ordering to do your aggregation more quickly versus, well, if the group by is based on the join key, then you can merge the aggregation inside of the join or reuse the same hash table, but that's not always the case. But there may be the case where, again, if you need a data to be sorted a certain way up above in the query plan, then maybe you actually do want to do the sort merge join, because you need to rely on the fact that the data's actually been pre-sorted for you. You pay the cost of sorting it, and you can reuse the sort of data multiple times. So we're going to see sort merge joins are variations of a join algorithm that relies on sort of data next class, because that's when we'll talk about the multi-way, the worst-case optimal joins, right? If you haven't looked at those papers yet, the big assumption that they make is that in order to do that comparison across multiple tables at the same time, you need to have your input data be sorted. So it's going to look a lot like the sort merge join algorithm that's just the data structure they're going to use to do the probes of comparisons across more than two tables is going to be slightly more sophisticated, instead of just a single iterator going, you know, just doing the straight comparison across two tables. OK? That's my favorite all-time job. What is it? Yes. It's the SD Cricut IVES. I make a mess unless I can do it like a GEO. Ice cube with the G to the E to the T. Now here comes Duke. I play the game, wears no roots. Homies on the cuss of yama, focus on drink proof. Put the bus a cap on the ice, bro. Bushwick on the go with a blow to the ice. Here I come. Will he eat? That's me. Rolling with fifth one. South Park and South Central G. By the 12-pack case, I'm a five. Six-pack, 48, gets the real price. I drink proof, but yo, I drink it by the 12 hours. They say bill makes you fat. But saying eyes are straight, so it really don't matter.


As I said we'll present an overview of financial performance in the annual report and accounts that landed just before Christmas. I'll also highlight previous 2 years, priors to show trends and how things have moved. And will also include some projections for the current year clearly, on audited looking at our revenues, looking at costs, looking at profitability and looking at our balance sheet. Having said all that, the story of the accounts in 2016-17 is relatively simple. Mae'n ystod y ffordd y mae gennymio'r newid yma, ac mae'n rhan o 4 pwn i'r 5 gwaith hon yn gweithio'r perlwni sy'n gwneud yn eu cyfnodd y Prrymu Lleidol Tee. Gweithio'n cyffredinol yn gallu gwneud yma yn rhoi ei ddweud ac mae'n cyfrifio'r gwaith yn fud i'r gwasanaeth a'i gweithio'r cyfnodd hwnnw. Mae'n cyfrifio'r cyfrifio'r gweithio yn llwy answers ac mae'n cyfrifio'r gweithio'r gweithbill And we've also had phenomenal significant support from our new majority shareholder. In And So Mon, that's the story of the Wel yn enghraif literally plac there's nearly two million pounds on offer for every place on the table in prize money. There's also 1.1 million for every live TV picks, so every time we're on TV or alive. Ho more away it's over a million pounds which is comfortably more than the gate receipts we get from any premier league fixture at Goodison. Match days are very simple story and I think are very impressive story. It is that story of season ticket record sales of caps on numbers ychydig, o'r llichach yn gydig o gydig y sgol iawn i'r prysau. Rhai yna llich, y band cofeil Sweidian, o Budd G وأf yn dwylo'r fan yn ei ddweud. Yn dweud sleidian yn dydig, yi ddweud Roedder bobl hwnnw, sy'n gweithio'r gwrth gwaith yma unrhyw arno. Mae'r ddweud o'r gwybod yma felly yna yn ei ddweud y gydig dim, aoi yr hollwy o'r ddweud sy'n ddweud iddyn nhw'n gydig sy'n ddweud i'r hyn mawr Gŷwys has gone down and that is a reflection of our commitment to young fans. The chart in the top right presents the really impressive information on capacities on season ticket no opposition, it is not that long ago, if you can see them there looking where we were in 2011-2012 and we are significantly further on from that, so all the hard work that has gone into this means, I guess, simply that it is no longer easy to get a ticket at Goodison Park. Mae'r cymunedau o welid yn cael ei ddiwedd. Mae'n ddiwedd i wneud i chi i gweithio dechreu ar gyfer a'r band, ac rwy'n cael ei ddweud gan erbyn ymmwy yw'r cyfrydd flynyngol yn 2018-19. Mae'r hyn yn sungai'r cyfrif Majesty Ticket yn y 4th ynvernig. Maen nhw'r fath jwg yn fynnu. Mae'r cyfrif Majesty Ticket yn llwyddiad yma ond llwy. Mae'r cyfrif Majesty Ticket yn llwyddiad. Wy corresponding it so real continued commitment to affordable football. Commercial revenue has grown significantly in the current year a record breaking deal from USM to be follow by equal record breaking deals to be reported in this years numbers from Sport Pacer and Angry Birds. Mae'r sorg paser oes yn ychydig i'ch cyfnodd fan o'r ffarn. Yn y gwaelwch â'r fideo o'r ffarnedig ynglynllwch ynglynig ynglynig o'r gweithlo i'r ffarnedig i east-Africa a fyddwn i'r 50,000 fan i'r Daerys-a-lam. Ymweld y Ddiol angri ac i chi'n gweithio'r ffarnedig yw'r ysgolfydd, Ieithio i'r cyfnodr y sport. Mae'n ddweud i'n meddwl am gweithio gweithio a gweithio'r byddwyd pan dweud i'r byw o'r ymdeg a gofodd yn fwy yn ei ddweud o'r cymdeithasol. Rwy'n ffordd i'r rhain i chi'n cymdeithasol i'r bandwch a'i ddiogelio'r ddau ac rwy'n ddweud i chi'n ddweud i chi'n ddweud i chi'n ddweud o'r partnerio cymdeithnu o'r cyfnod. bethdy'r nhw'n gwahanol o'r tynno'r tynnu'r wych amlletydd yn dwy'n gwahanol gweld y Cyflwyd-fawr yn gyfwyrdau maennau'r unigol byddio heb yn cael ei gynnwys gydig iawn. Felly, mae'n benderlineu mwy o'r cydnwys gwahanol yn ddweud yn argyflwyd pan allan fel yw'r marwm am 8 miliwn ar y cyfrifwyr eu teulu yn eu ffordd. Rwy'n ei ffrifwyr yn gŵi y gydwyr nae yn ei gŵyr, yne gyd yn eich cynnig o'r sefydig a yna'r gwahanol. Unrhyw gwerth i fy wneud o'r gyfer, a'r cyfwbydd yna'r ei hwn yn ei ddweud y blaes. Mae'n bwysig yn y llyfr o'r cyfrosio ddechrau o gyfwbydd hach i 240 miliwn. Mae gennym yw ymwnaeth bod 70% o bwysig anhygotu. Ond maen nhw sydd wedi chyfwyr am y ddisgu yn eu rhan o'r gweithgau i gyfrosio, wedi gyrdwyr sio o'n fwylo sy'n mlyneddol i amgylchaeth honno. Ond oedd ei chwyr sy'n byw y cyfrosio i ddiweddillau i gyd yn yr hynod o'i gyd. Fydd y llaw'r gwybodaeth yn gôl i'r gwirio ddarchodr iawn, yn gofyn mewn rhoi'n gwybodaeth arall. Fydd y llaw'r gwybodaeth yn gwybodaeth, nhw'n gweld gennych, yn y gymryd, mae'n fIFO cyllt yn ei wneud. Felly, dylai'r llaw'r gwybodaeth yn gwybodaeth sydd wrth eich siwr nhw'n gwirio ddaeth. Thew'r gwybodaeth o gwybodaeth OAQC3, rwyng sgwrn, thew'r gwybodaeth o'r prydentio lwn i'r cyfrwng. Ac mae gennyn nhw'n meddylé a'r byw yw chi'n cael ei ddweithio i'r grannu newydd yma yma, i fydden nhw'n gwybod neb yn cael ei wneud hefyd o'r grannu'r adroddau. Mae'r cyfrannu gymhreili maen nhw wedi'u gtwain y won. Caerddwch ei ddaethce fe o'r newefyr yma, yn gweithio i'n mynd i ddweud am ff入an hwn��u. Mae'n au bod yn dweud o'ch cyfrannu yma i mwyn roi. dweud yw 85p yn y pwn sy'n dweud yw'n dweud yw'n ysgrifennu Ffinchfarn. Mae'r prydyn sy'n dweud o'r gweithio ffynuol yn cael eu ddweud, ac mae'n dweud yw'n dweud yw'n dweud cymdeithasol sy'n dweud y dweud eich dweud o'r dysgu cymdeithasol sy'n dweud i'n dweud. Mae'n dweud yn ysgrifennu hefydlu. Mae'n dweud yw'n dweud i'r ddweud i'r ddechrau. The next chart explains our net transfer spend over the past seven windows. In really simple terms, you can see that for every one pound we've received from the sale of a player, we've spent two. We've invested almost £340 million over seven windows, with half of it recovered through the sales of Lukaku, Stones, Delafugh and Naesmith, so substantial investment in new playing talent.that player trading impacts our profitability in two ways. For the players on the top half of the diagram, there is the amatisation, the right off of their transfer fee over the life of the contract. For the players on the bottom half of the slide, for the sales then it's any profit or loss we realise in their disposal. So, moving into profitability next, the next chart really shows that there is no real pattern or trend in profitability. yr unig fod yn ydy'r gŵr, intensaeth, rymes, a amser lleolio, gyd-dyn ni'n symud i'r ffrom plwiad. Mae'n effeithio yn ffrom bywyd ar y blaenau a'r honi'n gwybod yn leidio i'r eistedd peiliad mewn parhau sy'n dod, byddwch yn gwneud y ffrom ymlaen, rydyn ni'n ar ôl yn lleoli'r ffrom ymlaen o'r ffrom plwiad. Mae'r ffrom mae'n gweithio ma'r yr oedden nhaw Lwcag yn ôl ddodd yn rhaid yn rhaid o'r ffordd of writing off the transfer fees of all the players we've signed. Okay, so, moving into our balance sheet, it's a different looking Everton balance sheet at the end of May 2017. We still have the same recurring issue if you like of understated assets and you look at our playing squad and it is significantly understated. What is good to see is the insured value relative to the book value remains high, remains positive. What's also really good to see on that chart is the young players on there. The young players that are probably on our books at nil are very limited values. And I think we'll all agree that the emergence of that young talent into the first team's squad in the first half of the season has probably been our biggest success of the season today and we should pay tribute to the work of our academy and David Hunzworth in particular in that respect. Our balance sheet has also changed from Mr Mishiri's substantial investment. I talked about it on one of the early slides. is investment has allowed us to repay all long term debt, so the prudential loan that sat on our balance sheet since 2003 has been fully repaid. That saves us around £3 million every season in interest charges and repayments. In terms of overall indebtedness on our balance sheet, well three things to really emphasise on this chart. At May 17 at the year end, besides amounts that we owe to other clubs for the purchase of players, no overdraft and no debt. Also at that same date Mr Michir is investment with no fixed repayment date and no contractual obligation to repay is shown in equity rather than in liabilities, so at that point we have a healthy shareholder fund balance of over £90 million. The significant spending we did in the summer of 2017 on players however means that we have additional new facilities with ICBC and Santander. Overall the financial position at Everton with the support of Mr Michir who we feel is very healthy and very strong. Mr Michir's investment has also allowed us to push forward on other areas. Substantial upgrades at Finch Farm and I'm sure all the redevelopment are good as part that we've all seen the way that Everton looks particularly on the floodlights right now is amazing and the investment is there to be seen. We've also seen the big investment in players and we saw on a previous slide the difference between what we'd spent on players and what we've brought in. So Mr Michir's contribution has also had a major difference there. We've also pushed forward and made significant advancements on the new stadiums securing the land and making initial progress on design and planning application. So next section tonight is to really give an update on where we are with the stadium. Significant resource time commitment and as you've seen money has gone into the new stadium project. We remain absolutely fully committed, we remain very optimistic but there are still some big hurdles for us to overcome. Not least finding all the money for what will be a significant financial commitment with long term lasting implications, consequences on the football club. Where are we going? I think everybody knows where we're going. We're going to Bramleymore Dot which is a site that commands a substantial premium. Not just the cost of the land but the cost of preparing the land for a new stadium. But of course it's a site that comes with fantastic commercial opportunities and upsides that our business plan continues to work on and reflect. What are we building? Well there are some outline, designs done, outline designs that were done to inform our costs but the most important thing we need to do next is consult with you our fans. We're convinced that our vision is one that you will endorse and that's a stadium that will challenge the way a new stadium been built over the past two decades. A stadium that very firmly puts football in the fans first. A stadium that's very firmly atmospheric and intimidating and we'll hear a little bit shortly from Dan Meese on some of those qualities. How much? Well it's an escalating cost. It's a cost that continues to grow but it's a cost that we continue to try and manage. Of course that increasing cost is stretching our funding target all the time which leads on to what has always been our biggest single challenge which is how we're going to pay for it. Well I'm delighted to be able to say that we're getting very close to securing a major funding deal with Liverpool City Council. We've been great partners with us over the past 12 months but around two thirds of the total project cost we are optimistic will be met by support from Liverpool City Council in a deal that makes great sense for the council that delivers a very healthy return to the council but most importantly acts as a catalyst and a springboard for all that fantastic regeneration, new jobs, new homes, new businesses in north Liverpool. What's next? Well what's very important to us, what's very important to us is to confirm Liverpool City Council support. With two thirds of the funding in the bag then the attention turns to the final third and work's already ongoing looking at how we're going to find the money for that final third. We will then move into a consultation and I'll talk a little bit very soon about how we will frame that consultation. We're already advanced making preparations for a planning application and as I said before our business plan is constantly reiterated looking at opportunities but also looking very closely at cost and risk. When will it be ready? We anticipate and we hope a year for design, a year to secure funding and a year to determine a planning application all obviously operating in parallel. That will allow us to start working around a year's time with a three year construction period on what is a challenging site to build a new stadium. If everything goes to plan to be in there kicking off in August 2022. Committed absolutely optimistic yes but you know the enduring message I think is the still challenges for us to overcome. As I said an important next stage for us will be to embark on consultation with our fans so giving you the chance to have your input and build the stadium that we all want and we all want to build. In that respect listening to all the informal feedback we've had from you over the last 12 months listening to industry experts visiting stadia all over the country and overseas. We've developed some core principles which will help frame the dialogue with you and frame the feedback in terms of the attributes and features we want from our new stadium. First of all we absolutely want it to be our fortress intimidating atmospheric a place where opposing teams hate to come. Reference before the two million pound per league place the most important revenue stream in our business plan will come from finishing higher and higher up the league to make this a truly home fortress will be really important in that respect that's number one priority. Number two is to make it home a home that's readily loved often visited fundamentally a stadium that everybody I think feels very proud of so it's very important that it's adopted quickly and easily. Of course one of the real drivers the real driver behind the movies to create a platform for growth so to be able to realise those waiting lists to be able to introduce new commercial partners to host new events essentially to remove the constraints the commercial constraints of goodison park. It needs to reflect our values it needs to reflect everything about the people's club it needs to be able to and we will maintain our commitment to affordable pricing not take advantage of perfect sightlines or the absence of pillars we need to maintain our values it also needs to be a landmark something that befits enhances complements our world famous waterfront fundamentally easy to get to and easy to get home from something we probably take for granted at goodison but something obviously so fundamental to any sporting event or sporting venue truly will never leave Liverpool 4 and Denise will talk a little bit about our powerful legacy program we won't be leaving Liverpool 4 we will leave a strong and powerful and enduring legacy it needs to respect the environment in the sense of respecting the rich maritime history of the city and the darks it needs to enhance the environment it's in a unique environmental location technology will be a big part of it connecting communities on a match day allowing us to entertain and transact but also to act to act as a technological hope on non-match days bringing a wider community together together through technology and finally as I said it absolutely has to be the right deal for the city it has to be a deal that generates return relieves pressure on council budgets but as I said also acts as that springboard for the regeneration of north Liverpool


h uh trevon lorence nice premier level two 199 congrats on that it's a great box to select man holy cow silver and jones and ian book that will do it for uh your break there um ildica or ildico thank you again for your sport we'll get them right out to you buddy i dude it's unbelievable you cannot stop we literally can't stop pulling mac jones select tonight it's unbelievable crazy i'll snag a pic of that when you uh yeah perfect uh you can fed access to big enough order i think where it's probably fine i think so too i think he was kind of screwed with patricia yeah yeah that's also true all right we'll grab two star wars galaxies and a sterling and i'll probably do it for tonight two galaxies and a sterling be right back guys rookie excellence one of one all right guys we have a big card here we have a big card here um what's the order number sr all right you're not john l by any chance are you if so it got ripped over on drip so i could drop you the link to that if you want are you you're not john l by any chance are you yeah let me know the um the order number and i could see if we have it ripped or not they're all clown dude i'm gonna grab that gala those two galaxies and i'll be right back that'll go straight into a mag wow oh that is a that's a lucas sushi image variation orange rookie five of 25 big card wow i've never pulled i haven't pulled one of these yet the variations are sick got one already let's go that i am so jealous of that jackie robinson corbin burns there you go first one of the day one of one craig abesio a whole time right there literally that is so sick that's those 2000s astros jerseys too wow who's got houston congrats h town first refractor in a gold wave f1 racer uh rookie joe guanyu that's really nice these inserts are really cool love the color on that that's sick her and reds oh there's one turn but redsox legends here going back to regina triple auto four out of fjord he's in rapid devils wow sick and grinds rookie and he's not expecting that stuff curry jersey number holy cow there's the 30 i saw the warriors and i was like oh kaminga or moody that'll be cool nope no sir not at all we'll get that magged up for you hang up of course shohyo tani triple patch two of three angels very nice eight rbi 13 k that is a sick patch in the center too cody bellinger yeah you're good john that's uh 156 let me see really quick 156 yeah that was ripped on drip here i could drop you the link to that really quick john absolute share copy john here you go here's the link for that for you that is over on the drip personal channel so sorry about that a little bit of confusion there um all right good luck here daniel let me write this out yeah that one they both ripped on drip the finest did the absolute did and you had the uh the three finest did is so three finest and one absolute they all ripped just go on to that other channel and you'll see for john l um what was i gonna do here i just see one absolute box so did she only so did you want one ripped on yeah yeah no i see the other one so do you want the other one hold on let me figure this out really quick two box mixer for john l was so you had the star wars black and then you had a finest okay i see that and then you had a that's not you that's roman kyle gill scott john there's one absolute update series what's up smack davo spanica p hit mosaic sam i don't so you only had one ripped on drip and you or so you ordered two and only one of them went on drip that's weird i don't know why that happened did you request to want for one to be ripped on drip and one not to be john because yeah i see you have two uh two absolutes and this one was okay okay okay okay all right that makes sense here let me i can go grab that i'll do that first yeah yeah i got you i got you all right i got you um here let me i i see i was very honestly i was very confused when i came in today and let me do that one really quick so i think it was all right i'm gonna have to check here let me let me grab i'm gonna grab these really the absolute really quick yeah we're good we're good wait no no no you're good you're good john that's not that's not you that's not you that was uh miscommunication i wasn't i wasn't told something i probably should have been told that's not you at all don't worry um all right so you're 152696 that's the one that needs to get ripped so i have to remove this tag all right so we'll rip yours now and then i'll rip this on youtube all right let me write this out do that after tuesday one five two six nine six okay so that's the one we need to rip perfect good luck here john let's do it buddy what's going on everyone say i'm here with latent sports cards ripping one box of 22 panini absolute football hobby for john l it's good luck here john let's do it man see what we got base there's a brendan ike that it's going to be tools of the trade 181 out of 199 christian harris that is 32 out of 75 and josh allen 57 out of 75 for the bills it's just neither illegal neither can answer that one for you buddy next box john good luck or next mini box i should say trade bond walker 16 out of 75 for the jazz there's tyron williams the 275 for the rands and a mackerel jumbo 43 out of 99 rookie premier jersey auto nice one there quarterback for the old panthers and last mini box john good luck buddy see what we've got zoneman night 48 out of 100 base and base that's kind of cool i like this duo it's going to be quenya williams and sauce gardener 77 of 99 for the jets and then this is really cool mercury morris 24 out of 199 for the dolphins congrats on that one of the best running backs of all time iconic ink that'll do it for your break there john thanks again for your patience sorry about the confusion there we'll get those right out to you buddy okay now that makes sense now we're good yeah no problem john sorry again for the confusion on that one you are good and ready to go now we're gonna knock daniel's out that'll probably do it for the night sir that's that uh you got the okay got it perfect yep there'll be personals tomorrow after breaks uh ted yeah so it'll be uh scott the nick list and ted that's what it'll go so i do apologize not being able to get to every single one of them but i do work tomorrow night so kind of have to just call uh have a pretty hard cut at uh at two so good luck here uh daniel what's going on everyone sam here with late in sports cards ripping uh two boxes of 22 top star wars chrome galaxy for daniel l so good luck here daniel let's do it buddy a wookiee's wrath refractor there's vintage enter darth vader refractor this is a really cool one too it's one of my favorites in the set got atomic luke skywalker out 70 of 150 card number one there you go daniel congrats buddy heck yeah man that's awesome we'll get that mag for you of course big time hit on the portrait right there big time portrait hit uh lack of faith in cob vent quigan refractor emperor palpatine these c3p others a kylo ren refractor nice one there stormtrooper atop a dubeck uh wedge and tillies sketch refractor tuskens and their bantha refractor as well and maybe the empire r2d2 sketch portrait refractor general grievous on the refractor as well base base snow troopers invade hawks green auto is going to be klatu that is eight out of 99 we've got quarry d williams is klatu on the green nice one there omega wave uh is chubaca and han solo and the cockpit nice one there that is 60 out of 99 han solo and chubaca and the cockpit on the wave refractor of uh the cob vent yeah cob vent i know like every its name of every card by heart now more in guard refractor x wings in formation and uh what is that bounty hunters answer the empire's call base scram off tarkin refractor han solo the uh was it smuggler yeah and patrolling the cantina on your base uh star wars droid works gameplay galaxy skywalker on the refractor portrait nice one there landal calrissian base yeah i agree earth 100 agree on that one there's leo uh organa in our boush disguise stormtrooper surveying the desert refractor rebel strike tech refractor harris and doula and base there's uh vintage leo organa refractor as well base green vintage star wars art card that is v 11 30 at 6 of 99 extra color in this very nice inside the meditation chamber refractor and base on to box number two good luck daniel i'm gonna pull sketch out of this now oh yeah this is both are really good i do like star wars black as well refractor i love how you get the one color parallel in the pack itself sometimes you get two which is really nice but like the golds and the supers and stuff are really sick vintage wedge antilles in battle i still remember in a one box personal the anakin skywalker super came out i pulled the anakin skywalker super fracter of uh hayden christiansen harris and doula refractor creatures of the cantina refractor so cool dude that is star wars or uh what's called bobe effect ship uh dark turn for anakin skywalker refractor yeah dude that was by far my biggest star wars pole for sure probably we'll never top that aboard the bridge refractor i don't really know how there is thought i don't really know how you can do yeah or you have to be like the best card in this set the super like yoda on the portrait refractor or like it'd have to be like a super of like a dual of like hairs and boarding chair that's like about as good as yeah i would say that's insane there's uh original trilogy concept leg organa in our boush disguise so we've got an auto coming up here it's gonna be the shadow of the dark lord and dr pershing that is omed apahi as dr pershing it's up dredden or dresden i should say dresden what's up good morning she's wedging tillies greedo refractor base it is possible to pull a sketch and a echo refractor sketch and a um that was a number right uh sketch and an auto in the same box it's happened a few times so it's still possible there's wave is uh sort of that snow troopers invade hoth uh no attack on hoth 72 of 99 on the wave nice one there really good box overall i mean the luke does it for sure but prism han solo portraits 29 of 75 unbelievable unbelievable two numbered portraits in the same box that's crazy two of the yeah two of the biggest characters arguably wow holy cow daniel congrats man we'll get that magged as well rebel star wars rebel assault and base that's crazy crazy crazy lord vader and his storm troopers refractor and han solo refractor at this point add in a nice portrait refractor star wars star fighter r2g2 portrait refractor as well base our salacious b crumb refractor as well number palpatine and storm trooper at top of and green star wars super bombad racing one of 99 on the gameplay galaxy so extra color in both the boxes medical droid refractor so really good really good two boxes really good there's a vintage yodan daigoba and a base that'll do it for your break there daniel congrats man that was awesome unbelievable we'll get that right out to you man yeah you could buy you could buy him on ebay you can buy like complete sets i would say smack um all right guys that's gonna do it for me tonight here is your break schedule forest starting tomorrow at 10 with f1 dynasty random left side serial numbers as well as phoenix uh then you got me starting at five tomorrow with allen and ginter chrome and f1 dynasty and phoenix as well so thank you guys again i will see you tomorrow have a good one


Over 116,000 people around this country, including health and medical workers, some of whom are in the gallery right now, have signed petitions calling on the government to support an immediate ceasefire in Gaza. There are over 2 million people in Gaza living in an area half the size of Canberra. 40% of them are children. And because of the blockade, they are walled in. They have nowhere to go. And so when there were plans to launch an invasion of Gaza, in the most densely populated place on the planet, it was obvious that it would lead to a humanitarian catastrophe. Yet as bombs began to rain down on Gaza, Labor and the Liberals backed the invasion. On top of the 1,200 Israelis dead, over 11,000 dead now in Gaza. Over 4,000 children dead. And they're just the ones we know of because they have lost the ability to count how many people are now dying. No food, no water, no electricity. Doctors performing caesareans by torchlight to mothers without pain relief. Premature babies unable to be incubated. And yet, even as these horrors unfold, even as entire families lie beneath the rubble, a Labor government still will not call for a ceasefire. We need to join the majority of countries around the world who are calling for a ceasefire. I seek leave to table the petitions relating to ceasefire in Gaza. Ceasefire now. Release the hostages in the occupation.


This is our process too since you talked to your process, so ours is very simple to live in.  Our first phase is our current phase which is our planning phase. Once we move into phase two, today is our execution phase and that's when we will execute our pre-planned target list. Because our battalion talk is not here right now.


We have just concluded a very useful meeting in lunch with King Hussein. We all recognize that the positive atmosphere which has developed in the Middle East recently can be credited in great measure to His Majesty King Hussein. Steps he has taken over the last year gave new momentum to the search for peace. Our discussions today have provided further evidence of Jordan's commitment to a peaceful resolution of the Middle East conflicts which should prompt a sense of gratitude from men of goodwill everywhere. The United States has long played a central role in the Middle East peace process. We're proud of what we've helped accomplish, and we look forward to continuing to make meaningful contributions. But we hope that His Majesty's courageous steps forward can lead to direct negotiations between the parties based on United Nations Security Council resolutions 242 and 338 by the end of this year, and we'll do our part to help bring this about. Our goal remains a just, lasting, and comprehensive peace which will satisfy the legitimate rights of the Palestinian people and provide for the security of all states in the region, including Israel. We recognize Jordan's economic and security needs, and in the spirit of working together, I have told the king that we will be able to count on the United States for assistance in addressing problems which Jordan may face in those areas. We are pleased and proud to have had His Majesty here with us today. Thank you very much indeed, Mr. President, for your kind words. Ladies and gentlemen, I have had a full, friendly, and useful discussion with the President on all issues of mutual concern. Regarding the prospects of peace in our area, I have told the President that a just, comprehensive, and durable peace in the Middle East should secure the legitimate rights of the Palestinian people, including their right of self-determination within the context of a Jordanian-Palestinian confederation. I have also assured the President that on the basis of the Jordan PLO Accord of 11th February, and as a result of my recent talks with the PLO, and in view of our genuine desire for peace, we are willing to negotiate within the context of an international conference a peaceful settlement on the basis of the permanent United Nations resolutions including Security Council resolutions 242 and 338. We are offering a unique opportunity for peace which might not be with us for long. I hope the United States, under the courageous and dedicated leadership of President Reagan, will find a way to seize this opportunity and respond positively to our peace efforts. The active and balanced role of the United States is an essential element for the success of the peace process. I would like to thank the President for his hospitality and kind words and wish him continued good health and every success. In that regard, it is our hope that an international conference would enable the parties to the conflict to negotiate the establishment of a just and durable peace in the Middle East. We need the international umbrella to offer us the opportunity to negotiate and when I speak of negotiations, I obviously mean negotiations amongst the party to the conflict. In other words, negotiations between the Arab side, in this case a Jordanian-Palestinian delegation with Israel on the other side. This is under discussion and we have not resolved some differences that we have in views on this, but we are going to certainly continue in these discussions. We have made it very plain here to for and nothing has changed with regard to those conditions under which we would meet with the PLO. Well, I can cite an example of the international conference of 1973. We met and negotiations were carried out between the Arab side and Israel. This is, I believe, a last chance for peace, we are approaching it. As I've explained, determined to do all we could for the establishment of a just and durable peace in our area and obviously when we speak of negotiations, we speak of them within the context of an international conference, but negotiations amongst the parties to the conflict. Well, we certainly are approaching the whole issue, not in a belligerent fashion. I'm almost sure of that. What I have said in my statement is the result of my discussions with the PLO, yes. I'm certainly hoping very, very much indeed that we will see some progress this year, yes. This is all being worked on right now with us or together. This is what we are discussing. Who's he asking? Which one of us? We think that the situation in Lebanon with regard to the peace process will be resolved completely when Israel has made its complete withdrawal from Lebanon. Wait a minute. There's a young lady over here who's been trying to... Oh, no. The people we have working there are going to continue. Well... I'm not going to respond to that question because, as I say, we're still discussing this whole matter and I'm not going to get into any great details, things of that kind. As I say, just generally, we're discussing and hopeful of arriving at a solution. I've spoken of an international conference and of the five-minute members of the Security Council. Well, that's what we are all working on, as the President has said. Yes, sir. I'm saying that. Every word I've made in my statement is a result of agreement between us and the Fialo. Fialo. Yes. Wait, wait, one sec. Wait, wait. Wait. Now, what? His Majesty has said that they've discussed this and, yes, that this... Well, as I've said, our terms have been made very plain for quite some time as to what is necessary for us to negotiate with the Fialo and they remain unchanged. I think we've said enough, sir. No. Well, because... Oh, well, I will answer then this word that this was the last chance and then this is the last we're going to take. The last chance... I think that the conditions have never been more right than they are now to pursue this peace. And who knows whether those conditions will ever come as close together again as they have now, so that's why I think the term last chance. And I think we ought to keep that in mind that perhaps it is the last chance. And now we're not going to take any more questions and I feel a few drops of rain and it doesn't bother His Majesty or me, but we won't want any of you to get wet, so... Thank you.