 Welcome to the session on fragment and replicate join in the parallel databases. This is one of the inter-operation technique. At the end of this session you will be able to apply this fragment and replicate join on given relations. So earlier we have seen some inter-operation parallelism techniques like parallel sort operation, parallel join operation in that we have seen partition join also. So the videos are available for this one. Today we will be talking about fragment and replicate join which is one of the parallel join technique. So this fragment and replicate join basically used where the non-equi-joint conditions are there. For example, you can say that where partitioning is not possible for the semi-joint conditions that is equality conditions. Like non-equi-joint conditions you may say the relation r with attribute a which is greater than relation s attribute b something like that. So the conditions which we are taking for comparison operator may be except equal to that is equi-joint. So basically fragment and replicate join is more suitable for non-equi-joint conditions. So the operations may be not equal to greater than equal to less than equal to less than greater than some of the things. So consider this example the query is something like that select name, mean price, maximum price, price, city from person joins with apartment on apartment id not equal to persons apartment id and price between minimum price and maximum price. So this is one of the query example. Let us see that how this will be executed for fragment and replicate join later I will show you the example relations also we will understand better there. So for joins where partitioning is not applicable. So what happens that everywhere for every join operation partitions may not be applicable ok, but we want to do the parallelization in that one. So in that case we are using fragment and replicate join ok. So fragment and replicate join is used for two things one is for the non-equi-joints and another one where partitioning is not applicable where we cannot do the partitioning. Now this fragment and replicate join is having one special case also which is called as asymmetric fragmented replicate join. Let us go ahead with the explanation about this one. There are few considerations here that one of the relations say R is partition. So what we are doing in the special cases which we call it as a asymmetric fragment and replicate join. So in this one one of the relation is partition and another relation is replicated therefore it is called as fragmented replicate. So here say that R is partition relation R and S we are taking. So relation R is partition and we can apply any partitioning technique on that one and the other relation S is replicated ok. Basically here what happens that if we have relation R and S where R is having more number of tuples than S then the bigger relation which is having large number of tuples is partition and the smaller one which is containing less number of tuples say S here that is replicated. And what the processor PI locally is doing as every processors are having the partition RI and the replicated version of relation called as S. So locally it is doing the join operation of RI with S. Let us see that in detail more later. So this is the depiction of fragment and replicate join where basically there are two forms here one is the this is the special case that is asymmetric fragment and replicate join and this is the fragmented actual fragmented replicate join. See here in the first one that is asymmetric fragment and replicate join which is the special case we can see in this one. What will happen that one of the relations say R we have seen earlier that R is partition so you can see the partitions here that is R0, R1, R2, R3 like this and the table the relation S is replicated. So the same copy is given to processor P0, P1, P2 and P3. So now how these are joining taking the join operation R0 joins with S, R1 joins with S, R2 joins with S, R3 joins with S. So here what happens every tuple of relation R is mapped with every compared with every tuple of relation S using this one then too we have done the parallelism here. Now another form is this one the actual fragment and replicate join. What has been done in this one that in this one both the relations are partitioned actually that is you can see here that R0, R1, R2, R3. So these are partition even S0, S1, S2, S3 these are also partition. What is replicated here see in this line you can see that horizontally if we are going for so how the join operation is taking place that is P0, 0 is doing the join operation of R0 with S0, P01 is doing the operation of R0 with S1, P02 is doing the join operation of R0 with S2 so on. So what will happen here that R0 is replicated on every processor whereas S is now fragmented you can see here partition. Similarly at this site what will happen that processor P00 is doing the join operation of R0 and S0, processor 1 0 is doing the join operation of R1 S0, processor P2 0 is doing the join operation of R2 S0. So here what happens S0 is replicated and what is partition that is relation R here. So these are the forms of the fragment and replicate join. Let us move ahead with some example. So the general case here what we are doing is it is reducing the size of the relations at each processor the normal fragment and replicate join requires more number of processor we have seen earlier also how you can see here that the partitions are these many but the processors are these one. Let us see how many processors we require here. So what is the scenario R is partitioned into n partitions you can say R0, R1, Rn-1, S is partitioned into m partitions S0, S1, SM-1. So we have n partitions of R, m partitions of S and we can apply any partitioning technique here. There might be how many number of processors are required m multiplied by n, why we have seen earlier okay the number of processors requires are more and we are labeling them like this one. What the PIJ processor we is doing it is taking the join operation of partition RI with partition SJ okay. In this case what happens RI is replicated to PI0. Similarly in the horizontally we have seen that SI is replicated to these processors okay and we can apply any joining technique for this one. Let us take an example here these are the example relations you can see there are two examples the person relation and the apartment we have two relations and we have the same query taken on this one and we want to do the join operation parallee you can see this is the data available and this is the query earlier we have seen this query that is we want to select name, minimum price, maximum price. So name, minimum price and maximum price from these one price and city from this relation and what is the join condition we want to do the join operation on person and apartment on where apartment ID okay ID of this one okay not equal to persons apartment ID so this one okay where it is not matching those one we want to get the data and what should be another condition and the price should be between minimum price to and maximum price whatever minimum and maximum is there that price we have to get the data from these one okay. So let us see the example with both the techniques. So this is a symmetric fragment and replicate join you can see here that the table I have to study here to show you properly so this is my table this table person table I have taken here and this is the apartment table and what we have done here person table I have divided with two partitions so the partitions are these one you can see this is partition zero and this is partition one whereas the replication of apartment relation is this one so this particular table is replicated at P0 also and it is replicated at P1 also okay and now what P0 is doing P0 is parallelly doing the join operation of partition P0 partition of this particular person data with the replicated data of apartment similarly persons and other partition parallelly is doing the join operation with the again apartment relation why because apartment relation is copied you can say replicated so that is what a symmetric fragment and replicate join is let us go ahead with another technique the actual fragment and replicate join for your purpose I have I have done like this so this is my person table which is fragmented into partitions partition 0 and partition 1 of person relation similarly the relation which is apartment I have taken into two partitions so this is partition P0 of apartment and partition P1 of apartment and now what we this processor will do P00 is doing the join operation of partition 0 with partition 0 of this similarly it is doing partition 0 of relation person with partition 1 of relation apartment similarly here also it is doing the partition the join operation of person 0th partition of person with the 0th partition of apartment similarly here also so automatically what happens all the every tuples of one relation are mapped compared with another relation but parallelly every processor is working here all these processors that is processor P00 P01 P10 P11 all these are parallelly working so again the same scenario we have two relations we have the query pause the video and try to write the result of this query so let us see what will be the result the result is like this you can see that the data the person ID which is not matching with this one okay apartment ID and this ID we have seen that apartments ID not equal to persons apartment ID so those data we have taken here and again another condition is what minimum price and maximum price the price should be between 40,000 to this one you can see for NA okay what is the apartment ID to so we are not taking the apartment ID to for NA we are taking the other apartment data so for one if we are matching we will see that the price is 30,000 which is in between the minimum and maximum so we will get this data here you can see that that data come here two will not come because it is matching three for three we can say that it is the condition is what the condition is this one so it will come down here again okay so like this it works so what we can say for fragment and duplicate join both the versions are working for every tuple of R is mapped with every tuple of S okay it is having higher cost because you can you can see that the partitioning is also there the replication is also there so basically it is having higher cost but more parallelism is implemented in this one okay but usually the symmetric fragment and replicate is preferable these are my references thank you