Databases Reference
In-Depth Information
In this operation, the database used NESTED LOOPS to fulfill the query. This algorithm
works as follows:
1. Read a row from the first dataset (the
CUSTOMERS_PK
index, in our example,
using an INDEX FAST FULL SCAN to scroll all index leaf nodes).
2. For each row of the first dataset query the second dataset (the
PK_MY_CUSTOMERS
index in our example, using an INDEX UNIQUE SCAN to locate the record that
matches the join condition).
3. Repeat from step 1 until the end of the first dataset is reached.
In the execution plan shown earlier, the last operation SORT AGGREGATE, calculates
the requested
COUNT(*)
value.
The use of a NESTED LOOP could be a problem if we need to access the major part of
the inner table and there isn't an index to speedup this operation.
In step 5, we query
MY_CUSTOMERS
and
MY_COUNTRIES
tables joined together
on
COUNTRY_ID
field, using an equi-join. We can see the execution plan in the
following screenshot:
