Example of query-plan execution

This topic contains an example of a query with a SELECT statement that calls for a three-way join and describes one possible query plan.

The following SELECT statement calls for a three-way join:

SELECT C.customer_num, O.order_num
   FROM customer C, orders O, items I
   WHERE C.customer_num = O.customer_num
      AND O.order_num = I.order_num

Assume also that no indexes are on any of the three tables. Suppose that the optimizer chooses the customer-orders-items path and the nested-loop join for both joins (in reality, the optimizer usually chooses a hash join for two tables without indexes on the join columns). Figure 1 shows the query plan, expressed in pseudocode. For information about interpreting query plan information, see Report that shows the query plan chosen by the optimizer.

Figure 1. A query plan in pseudocode

for each row in the customer table do:
   read the row into C
   for each row in the orders table do:
      read the row into O
      if O.customer_num = C.customer_num then
         for each row in the items table do:
            read the row into I
            if I.order_num = O.order_num then
               accept the row and send to user
            end if
         end for
      end if
   end for
end for

This procedure reads the following rows:

All rows of the customer table once
All rows of the orders table once for each row of the customer table
All rows of the items table once for each row of the customer-orders pair

This example does not describe the only possible query plan. Another plan merely reverses the roles of customer and orders: for each row of orders, it reads all rows of customer, looking for a matching customer_num. It reads the same number of rows in a different order and produces the same set of rows in a different order. In this example, no difference exists in the amount of work that the two possible query plans need to do.