Sort Merge Join

Prathap G
A free video tutorial from Prathap G
Technical Enterprise Architect
4.4 instructor rating • 3 courses • 11,088 students

Learn more from the full course

Oracle SQL : Mastering Oracle SQL Performance Tuning

Oracle SQL Query Tuning and Architecture | Solve critical Oracle SQL query performance issues | Advanced SQL Tuning

06:26:10 of on-demand video • Updated October 2019

  • Step by Step approach on troubleshooting badly performing SQL queries.
  • Identify how internal Oracle components affects the performance of the SQL query.
  • Identify potential performance bottlenecks by reading the Explain plan of the queries.
  • Tips and Tricks to make the Oracle SQL query run faster.
  • Compare BAD vs GOOD Queries with Examples.
  • Understand how STATISTICS and CARDINALITY affects the performance of the SQL Query.
  • Implications of JOINS and ACCESS Methods on the performance of the Query.
  • Oracle Performance Tuning on both Oracle11g and Oracle12c Environment.
  • Advanced SQL Query Tuning Steps.
English [Auto] Hey guys. Welcome back. In this list and a look at second tape of giant namely smart mouth joint as a name indicates this algorithm the sorting of the giant columns and then merges them to get the desired giant output a software giant is a variation of nested loop giant. The main difference is that this method requires that two data objects to be ordered first. And the algorithm to find the matching rules is more efficient than Master look joint. They perform better than nested loop when the volume of the data is big tables. But it is not good as a hash joint which we'd be going to the next list sent to join huge tables. However they perform better than has joint when the joint conditions are already sorted or there is no sorting required. The full operation is done in two parts. First is saw operation rid of data after joining columns are sorted if not already started. This is a mandate to this step. It will select the first row from both the tables. Second is much more efficient with a data set that emerged to get the result. This is a less good version of the algorithm used biological to perform more joint this of looking at the algorithm. I will show you how to do a merging with an example and then probably this algorithm would make sense to you. In this example we have two tables where the first table has employee and employee name information. And second people has employed I.T. and salary of the employee. Let us not join the two tables using employee equality. We will see how the Oracle uses must join to get the results. In this case the employee 80 column is already started and hence cited input is passed on to the merge operation. The result of the join is stored in a table. The first thing that it does is it features the third strike out of both the tables since the tables is already started based on employee as you could see that the pointer said pointing to the first recorded in each day but it will first check if the value selected for the pointers matches. It doesn't match you also see that the data in the first point guard is larger than the value. The second point and hence the pointer will move to the next record in the second table. So it now moves the pointer to eighty six double eight nine. It again does the comparison. Again the values do not match the data in the first point there is a larger than the value in the second quarter. So again the pointer will move to the next regarding second table. So it now moves the pointer to eighty seven thousand. It again does the comparison this time values match. So there are two things that happen simultaneously when the values match. First the giant data is selected and at the same time the pointer table is moved to the next checkout. So the appointed will allow more to next record and second table. So it now moves to bind the two eighty seven thousand one and at the same time it selects the joint data. It again does the comparison. Again the values do not match the data in the first point is smaller than the value in the second point. So this time the point that we moved the next record in the first table and not in the second table. So it is now most pointed to eighty seven thousand one in the first table it again does the comparison this time values match. So the pointer moves to the next stroke in the second table. So now most appointed to eighty seven thousand do and at the same time it selects the joint do that the same process happens for all the records that day so we have now reached a point where pointer in Table 1 is pointing to eighty seven thousand forty and pointing in table two is pointing to eighty seven thousand fifty it again does the comparison again the values to work match and the data in the first point are smaller than the value in the second point. So this time the pointer will move to the next record in the first table. Since there isn't any records in the first table the process stops and a merger operation continues. Let me now show you the pseudocode assortment. Join as we can see here that there is a loop quite a while loop which scans on the records in both the table once and during each operation. It checks for three conditions and performs a specific task. It will either move the pointer in the second table or the to planted in the first table joins the data and most a pointer. Guys I would like to be able to pause the video for a minute. Go through the video or understand the pseudocode completely and try to see how the most actually works in reality. Since now we have gone through the nested loop join an assortment join in this lesson. Let us look at another important type of Chime namely hash join next next lesson we with. Let me wrap this this way and see you on a mixed blessing.