Understanding Three-table Joins in Oracle

In a three-table join, Oracle joins two of the tables and joins the result with the third table. When the query in the following listing is executed, the EMP, DEPT, and ORDERS tables are joined together, as illustrated in Table 1.

 

 

 

 

 

 

 

 

Table 1. A three-table join

 

Which table is the driving table in a query? People often give different answers, depending on the query that accesses the PLAN_TABLE. This query would drive with the EMP table accessed first, the DEPT table accessed second, and the ORDERS table accessed third (there are always exceptions to the rule). This next listing shows a query that has only one possible way to be accessed (the subqueries must be accessed first) and a query to the PLAN_TABLE that will be used for the remainder of this article. This listing is provided to ensure that you understand how to read the output effectively.

 

 

 

 

 

 

 

 

The following listing is a quick and simple EXPLAIN PLAN query (given the PLAN_TABLE is empty). Throughout this article, I show many of these, but I also show the output using Autotrace (SET AUTOTRACE ON) and timing (SET TIMING ON).

 

EXPLAIN PLAN Output

Next, you can see abbreviated EXPLAIN PLAN output.

 

 

 

 

 

 

The order of access is PRODUCT_INFORMATION, ORDER_LINES, and CUSTOMERS. The innermost subquery (to the PRODUCT_INFORMATION table) must execute first so it can return the PRODUCT_ID to be used in the ORDER_LINES table (accessed second), which returns the CUSTOMER_ID that the CUSTOMERS table (accessed third) needs.

TIP

To ensure that you are reading your EXPLAIN PLAN correctly, run a query in which you are sure of the driving table (with nested subqueries). One exception to the previous subquery is shown here:

EXPLAIN PLAN Output

 

 

 

 

 

 

The expected order of table access is based on the order in the FROM clause: PRODUCT_INFORMATION, ORDER_LINES, and CUSTOMERS. The actual order of access is ORDER_LINES, PRODUCT_INFORMATION, and CUSTOMERS. The ORDER_LINES query takes the PRODUCT_ID from the subquery to the PRODUCT_INFORMATION table and executes first (Oracle is very efficient).