Multitable query

This topic shows sample SET EXPLAIN output for a multiple-table query.

Figure 1. Partial SET EXPLAIN output for a multi-table query

QUERY:
------
SELECT C.customer_num, O.order_num, SUM (I.total_price)
FROM customer C, orders O, items I
WHERE C.customer_num = O.customer_num
AND O.order_num = I.order_num
GROUP BY C.customer_num, O.order_num

Estimated Cost: 78
Estimated # of Rows Returned: 1
Temporary Files Required For: Group By

  1) virginia.o: SEQUENTIAL SCAN

  2) virginia.c: INDEX PATH

    (1) Index Keys: customer_num   (Key-Only)  (Serial, fragments: ALL)
        Lower Index Filter: 
          virginia.c.customer_num = virginia.o.customer_num
NESTED LOOP JOIN

  3) virginia.i: INDEX PATH

    (1) Index Keys: order_num   (Serial, fragments: ALL)
        Lower Index Filter: virginia.o.order_num = virginia.i.order_num
NESTED LOOP JOIN

The SET EXPLAIN output lists the order in which the database server accesses the tables and the access plan to read each table. The plan in Figure 1 indicates that the database server is to perform the following actions:

The database server is to read the orders table first.
Because no filter exists on the orders table, the database server must read all rows. Reading the table in physical order is the least expensive approach.
For each row of orders, the database server is to search for matching rows in the customer table.
The search uses the index on customer_num. The notation Key-Only means that only the index need be read for the customer table because only the c.customer_num column is used in the join and the output, and the column is an index key.

For each row of orders that has a matching customer_num, the database server is to search for a match in the items table using the index on order_num.