Nested loop of Oracle table join

The nested loop of Oracle table connection executes a query SQL statement in the database system. If this query only operates on one table, it only involves access to this table and associated objects. There are usually three access methods: full table scan, full index scan, and index scan. If the query operation is performed on two or more tables, the connection between the tables to be operated becomes crucial. When the database system executes this SQL statement, it will always be associated with two result sets. For example, to operate three tables on www.2cto.com, two of them are associated with a result set and the third table. When you operate on four tables, two of them are associated with a result set, the third table is associated with a result set, and the fourth table is associated with the last result set. Of course, it may also be that two result sets are associated to each other, and then the final result set is obtained. The association between five tables is more complex. These descriptions are used by the database system to generate different execution plans based on the different arrangement and combination of Operation tables. The Oracle database system uses a mechanism to determine which combination has the best performance. This mechanism is called the Cost-Based Optimization (CBO ). Because when multiple tables are operated, the two tables are joined to obtain the expected result set. The Oracle database system divides the two tables into three join modes. Here we will introduce the connection method of Nested Loops (NL. Nested loop, as its name implies, is to take a table as the starting point and traverse all records of the table one by one to record the other table one by one. The matching result set is written. For example, select. *, B * from EMP a, DEPT B where. DEPTNO = B. DEPTNO; if the emp table is used as the starting point, the records in the emp table are queried as m records, and then the deptno value of the field in the m records is returned, match the deptno field values of all records in the dept table one by one. Assume that the dept table has n records. The matched records are written into the result set if they meet the conditions. In this way, the number of Operation Records is: m in the emp table, n in the dept table, but m times, the total number of records is m + m * n. If dept table is used as the starting point to traverse the emp table, the total number of records is n + n * m. Different connection methods have different costs. CBO will go to the smallest one. This is the starting table. It is called an External table or a driver table in official terms. Pseudo code is used to indicate nested loop connection www.2cto.com declarebegin for outer_table in (select * from emp) loop for inner_table in (select * from dept where DEPTNO = outer_table.DEPTNO) loop limit (inner_table. *, outer_table. *); end loop; end; based on the characteristics of nested join, we can imagine that it is used when the data volume of the two associated tables varies greatly, however, the overall data volume should not be too large. This association method is suitable for querying small data volumes.