Oracle table join method

Oracle table join methods include nested loop join, hash join, sort-merge join, and flute product join. Each join method has certain conditions that are most suitable for use. For each pair of tables to be joined, the optimizer must also determine the sequence of table joining. NESTED loop join (nested loops) www.2cto.com NESTED loop join uses each row in the result set obtained by an access operation to touch the other table. If the size of the result set is limited and an index is built on the joined column, the join efficiency is usually the highest. The operation cost of nested loop join is mainly the cost required to read each row in the outer table and link it with the rows in the matched inner table. As the name suggests, nested loop join is a loop nested in another loop. An outer loop is basically a query that uses only the conditions in the WHERE clause that belong to the driving table. When the data rows are filtered by outer conditions and the matching conditions are confirmed, these rows enter the inner loop one by one. Then, perform a row-by-row check based on the joined columns to check whether it matches a row in the joined table. If this row matches the second check, it will be passed to the next step of the query plan or directly included in the final result set if there are no more steps. The strength of this type is that the memory used is very small. It is best when there are few result sets. The first table listed after the nested loops operation is the driving table. Www.2cto.com sorting-sort join merge join sorting-the merge join independently reads the two tables to be joined and sorts the data rows in each table by the JOIN key, then, merge the sorted data row sets. For this join method, the sorting overhead is very large. For data sources that cannot be stored in the memory, temporary disk space may be used for sorting. This is very memory-consuming and time-consuming. However, once the data row set is sorted, the merge process is very fast. In order to merge, the database takes turns to operate on the two tables. After the top data rows, the data rows that appear earlier than the top row in the sorting queue are discarded, only matched rows are returned. Hash join is similar to the sort-merge JOIN. First, the filter criteria in the WHERE clause are used to independently read the two tables to be joined. Based on the statistical information of tables and indexes, the tables identified as the minimum number of returned rows are completely hashed to the memory. The hash list contains all the data rows of the original table and is loaded into the hash bucket based on the random function that converts the join key to the hash value. As long as there is enough memory space, this hash will remain in the memory. However, if there is not enough memory, the hash will be written to the temporary disk space. The next step is to read another large table and apply the hash function to the join key column. Then, use the obtained hash value to test the small hash list in the memory to find the hash bucket where the row data of the matching first table is located. Each hash bucket has a list of data rows in it (represented by a bitmap ). This list is used to match the probe row. If the match succeeds, this row of data is returned; otherwise, this row is discarded. A large table on www.2cto.com is read only once, and each row is checked for matching. This is different from nested loop join because the inner table is read multiple times. Therefore, a large table is a driver table, which is read only once, while a small hash table is detected many times. Unlike the nested loop join execution plan, the smaller hash in the output of the execution plan is placed at the front and the larger detection table is placed at the back. Merge join cartesian join occurs when all rows in a table are joined to all rows in another table. Therefore, the number of rows in the result set obtained by this join operation is equal to the number of rows in one table (A) multiplied by the number of rows in another table (B, that is, A * B = the total number of data rows in the result set. The OUTER join (nested loops outer) returns all rows in one table and the row data in the other table that meets the join conditions. ORACLE uses + characters to indicate external join. + Signs are placed in a pair of parentheses, beside the join of the table that will return data rows only after matching. As pointed out in the summary of the concatenation methods, the outer join table must be used as the driving table. This means that a more optimized join execution sequence may not be selected. Therefore, you must be especially careful when using external connections because the selection may affect the performance of the entire execution plan. Www.2cto.com Summary: when determining the execution plan of SQL statements, the optimizer must make several key choices. First, determine the most suitable access method for each table used in the query. There are basically two options: Index scan or full table scan. The implementation of each access method to access data that contains SQL statements is different. Once the optimizer selects an access method, You must select a join method. The table is joined by a pair. The data row of the result of the previous join is used to join the next table until all tables are joined and the final result set is obtained. Understanding how each access and connection method is implemented helps you write the SQL statements that allow the optimizer to make the most efficient choices. Understanding the selected operations and how these operations are performed will also help you avoid the most prone to performance problems. Understanding the Internal principles in the table can help you write better and faster SQL statements.