Diagnose Table Joins using Oracle explain Plan

While the EXPLAIN PLAN utility is commonly used to view access paths, it can also be used to view a nested loop (as well as correlated subqueries). A nested loop is a type of execution plan used to join two or more Oracle tables. It scans one table, and for each row of that table probes for the corresponding rows in the other table.

When Oracle joins two or more tables with the rule-based optimizer, the order that the tables are specified in the FROM clause affects execution time for the nested loop query. The table that returns the fewest number of rows should be the driving table^[1], and the driving table should always be specified last in the FROM clause. In short, tables should be listed in the FROM clause in order from largest to smallest because the driving table is accessed first in the query.

    
Wrong: select * from tiny_table, small_table, large_table;

Right: select * from large_table, small_table, tiny_table;

The driving table is the table that returns the smallest number of rows in your query, not necessarily the table with the smallest number of table rows. You must carefully analyze your WHERE clause to determine the approximate number of rows returned by your query. To illustrate, consider the following table join.

select  
   student_name,
   course_number
from
   student s,
   registration  c
where
   s.student_id = c.student_id;

SELECT STATEMENT [CHOOSE] Cost=5 
NESTED LOOPS
   TABLE ACCESS FULL STUDENT [ANALYZED]
   TABLE ACCESS FULL REGISTRATION [ANALYZED]

Here we see the EXPLAIN PLAN for this query, showing the student table as the driving table and the registration table as the probed table. Sometime the use_hash hint with the cost-based optimizer will run far faster than nested loop joins. We will be covering this technique in detail in a later lesson. Even though we use the rule-based optimizer for these examples, the cost-based optimizer will also use nested loops. However, with the cost-based optimizer Oracle will use table statistics (rather than the position of the table name in the FROM clause) to determine the driving table. Now that we understand the tuning of basic table joins, let us explore a more complicated concept, the idea of Oracle subqueries.

[1] Driving table" This is the table used by the SQL optimizer in the initial step of execution.