Table join and adjustment in Oracle (1)

Only with a clear understanding of these issues can we select a suitable connection method for specific query needs and develop robust database applications. Selecting an appropriate table connection method has a crucial impact on the performance of SQL statements. Below we will give a brief introduction to some common connection methods and applicable scenarios in Oracle.

1. Nested Loop connection Nested Loop)

Nested loop connections work in the following way:

1. Oracle first selects a Table as the driving Table for the connection. This Table is also known as the External Table Outer Table ). A Table or data source that is connected by a driver Table is called an internal Table ).

2. Extract matching records from the driver table and associate them with the connected columns of the driver table to query matching records. In this process, Oracle first extracts the first record that meets the conditions in the driver table, and then associates it with the connection column of the internal table to query the corresponding record rows. During the association query process, Oracle will continuously extract other matching records from the driver table and the internal table association query. These two processes are performed in parallel, so the nested loop connection returns the first few records very quickly. It should be noted that, since the smallest Oracle IO unit is a single data block, in this process, Oracle will first extract all rows in a single data block that meets the conditions in the driver table, join the query with the internal table, and then extract the records in the next data block to continue the loop connection. Of course, if a single row record spans multiple data blocks, a single record is associated for query at a time.

3. The nested loop connection process is as follows:

NESTED LOOP

We can see that there are two loops. One is an external loop, which extracts each record that meets the conditions in the driving table. The other is an internal loop. The internal table is connected and queried based on each record extracted from the External Loop. Since these two loops are nested, the connection method is called nested loop connection.

Nested loop connections are suitable for queries with high selectivity and constraints, and only a small number of records are returned. Usually requires that the records of the driving table meet the conditions, usually through efficient index access) is relatively small, and the connected columns of the driving table have a unique index or a selective non-unique index, nested loop connections are highly efficient. For example, the following query is a typical example of nested loop connections:

SQL> select e.empno,e.ename,e.job,d.dname 2 from emp e,dept d 3 where e.deptno=d.deptno 4 and e.empno=7900; EMPNO ENAME JOB DNAME ---------- ---------- --------- -------------- 7900 JAMES CLERK SALES Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 3 2 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' 5 4 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

In this query, the optimizer selects emp as the driving table and quickly returns 7900 records that meet the condition empno according to the unique index PK_EMP, query the corresponding dname with the deptno associated with the drive table dept and return the result set. Because the deptno In the dept table has a unique index PK_DEPT, the query can quickly locate the record whose dname is SALES and return it.

The choice of nested loop connection driver tables is also a point that needs to be paid attention to during the connection. A common misunderstanding is that the driver table needs to be selected as a small table. In fact, this is not correct. Assume that two tables A and B are associated for query. Table A has 1000000 records and table B has 10000 records, but Table A only filters 10 records, at this time, it is obvious that using Table A as the driving table is more appropriate. Therefore, the driver table is the table with the minimum number of records returned by the filter condition, rather than the table size.

In external join queries, if nested loop connections are used, the driver table must not meet the condition association, that is, the table that does not add (+. This is because the external connection needs to extract records that may not meet the conditions in another table. Therefore, the driver table must be the one that we want to return all the tables that meet the conditions. For example, the following query selects the emp table as the driver table for connection:

Roby@XUE> select emp.ename,dept.dname 2  from emp,dept 3  where emp.deptno=dept.deptno(+); ENAME      DNAME ---------- -------------- SMITH ALLEN WARD       SALES JONES      RESEARCH MARTIN     SALES BLAKE      SALES CLARK      ACCOUNTING SCOTT      RESEARCH KING       ACCOUNTING TURNER     SALES ADAMS      RESEARCH JAMES      SALES FORD       RESEARCH MILLER     ACCOUNTING 14 rows selected. Execution Plan ---------------------------------------------------------- |   0 | SELECT STATEMENT             |         |    14 |   308 |    15 |   1 |  NESTED LOOPS OUTER          |         |    14 |   308 |    15 |   2 |   TABLE ACCESS FULL          | EMP     |    14 |   126 |     3 |   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1 |*  4 |    INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     0

The nested loop connection returns the records of the first few rows very quickly, because after a nested loop is used, you do not need to wait until all the loops end before returning the result set, instead, the query results are continuously returned. In this case, the end user will quickly obtain the first batch of returned records and wait for Oracle to process other records internally and return them. If the number of records of the driver table to be queried is very large, or the connected column of the driver table is not indexed or the index is not highly optional, the efficiency of nested loop join is very low.