Nested Loop - Operations Involved

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > Relation - Data Operator (Relational Algebra) > Relation Operator - Nested Loop Join

1 - About

A NESTED LOOPS operation joins two data sources. It indicates that an index is available for use during the join. As a row operation, NESTED LOOPS returns each row to the next operation as it is processed rather than waiting for the entire set to be processed. Because it is an index-based row operation, NESTED LOOPS is a very effective join operation for multi-user online applications (OLTP)

3 - Operations

When performing a NESTED LOOPS join, the optimizer first selects a driving table for the join. A full table scan may be performed on the driving table. For each row in the driving table, an indexed access to the driven table is performed to see if a match exists between the tables. If a matching value exists, that record is returned to the user via the NESTED LOOPS operation.

For example, consider the SALES and COMPANY tables. In the following query, COMPANY and SALES are joined by the Company_ID column. In the example, Company_ID is the primary key of the COMPANY table, and is the first column of a multi-column primary key in SALES.

SELECT COMPANY.Name
FROM COMPANY, SALES
WHERE COMPANY.Company_ID = SALES.Company_ID
AND Period_ID = 2;

When executing this query, the optimizer can use an index based on the join criteria in the WHERE clause. The execution path for the query is as follows:

NESTED LOOPS
TABLE ACCESS FULL SALES
TABLE ACCESS BY ROWID COMPANY
INDEX UNIQUE SCAN COMPANY_PK

The execution path shows that SALES will be the driving table for the query. The first step performed by the query is a full table scan of SALES. For each record in SALES, the COMPANY_PK index is scanned to see if a matching Company_ID value exists for the SALES. Company_ID value in the current row. If a match exists, then a table access by ROWID is performed to get the COMPANY.Name column requested by the query.

The steps for this example’s NESTED LOOPS operation are:

  • Full table scan of the driving table.
  • Index range scan of the driven table.
  • If a match is found, a table access by ROWID of the driven table.

As an index-based row application, NESTED LOOPS will be effective for joins performed in multi-user online application environments. For single-user batch processing, particularly if you are dealing with very large tables, you may need to force MERGE JOINS to occur to improve your throughput.

Since NESTED LOOPS joins, during their execution, reuse the index blocks they have already read into the SGA’s data block buffer cache, they generate an artificially high hit ratio for the instance.

Advertising