Connection Methods in Oracle execution plan: nestedloopsjoin, sortmergejoinn, and hashjoin

Connection Methods in Oracle execution plan: nestedloopsjoin, sortmergejoinn, and hashjoin

Connection Methods in Oracle execution plan: nested loops join, sort merge joinn, and hash join

Keywords: nested loops join, sort merge joinn, and hash join

Nested loop (Nested Loops (NL ))

If two tables A and B are nested and connected cyclically, Oracle will first extract A record from table A and then search for the matching record in Table B. If yes, push the information of this record to the result set waiting for return, and then extract the second record from Table A to find the second matched record in table B, if the match is true, it is pushed to the returned result set, and so on until all the data in Table A is processed, and the result set is returned, the nested loop join operation is completed.

(Hash) Hash Join (HJ ))

If there are two tables A and B for hash connection, ORACLE will first create A query Binary Tree C in the memory in the form of A hash list, then, read the first record of table A and find the matching record from Table C. If yes, push it to the result set. Extract the second record in A. If yes, push it to the result set, and so on until there is no record in A and return the result set.

(Merge) Sort Merge Join (SMJ ))

If two tables A and B are joined for sorting and merging, ORACLE first sorts Table A to form A temporary "table" C and then sorts table B, form a temporary "table" D, merge C and D, and return the result set.

From the perspective of the pre-obtained data volume, if Table B is involved in the calculation of a small amount of data, the efficiency of nested loop connections is relatively high, this is because the final result set can be obtained with very little IO. However, if the data volume is large, hash join and sort merge join are advantageous.

From the perspective of indexes, indexes can improve the efficiency of nested loops, because the operations for getting data from Table B are similar to querying data from a single table, the efficiency of table access full and by index must be different, but it also depends on the data size of B involved in the calculation, if the data in Table B is within the size of the data block that can be captured at a time, the index may not be used.

From the memory perspective, the memory usage of the same data volume nested loops should be the smallest, sort merge should be the largest, and hash join memory should be consumed in the middle. It is just a sensory intuition, but it has not been tested. Because sort merge needs to create two sorting tables, and hash join needs to create a query tree for table B.

From the hash point of view? It is estimated that all three tables use hash. hash is used to improve query efficiency, for example, 8 = power (2, 3). If hash is used, you may need to create a hash tree to increase the space consumption. If table access is full, you need to scan at least 1 time and at most 8 times. If hash is used, it can be obtained at least once and at most three times, and the time advantage can be obtained using space. In this case, at least the indexes and hash join in nested loops used in hash are felt.