Oracle Explain Plans: Driving Tables and Table Joins

By Dan Hotka on November 27, 2012


In Oracle, the driving table is the table that is joined to other tables in an SQL query. Understanding how Oracle identifies the driving table can help you make better tuning decisions.

How the RBO and CBO differ in identifying driving tables

When Oracle decides on an explain plan for a query, the RBO and CBO have different approaches for identifying the driving table.

For the Oracle Rules-based Optimizer (RBO):

  • The driving table is the last table in the FROM clause moving from right to left or first NESTED SELECT.

For the Oracle Cost-based Optimizer (CBO):

  • First Rows: looks for eliminating most rows via unique indexes
  • All Rows: looks for better throughput, possibly using Merge Join

NOTE

An ORDERED or LEADING hint makes CBO act like RBO

With the driving table identified, Oracle joins up two tables at a time.  An intermediate result set is created and passed to the next step up the explain plan tree.  The smaller the initial result set (i.e., if most of the rows are eliminated here) the faster the SQL will perform.

NOTE

This is the secret to SQL tuning:  eliminate most of the rows in the early steps of the execution.  You must first learn which WHERE CLAUSE predicates will eliminate most of the rows then make sure this selectivity is being utilized in the inner-most part of the execution.

Supported join types

Both the RBO and CBO support these three join types: nested loop, merge join and hash join.

Nested Loop

The driving table is read once and for each row in the driving table, the inner table is processed once.  The smaller the inner result set, the faster the Nested Loop will perform.

  • Larger result set should be the driving table
  • Smaller result set should be the inner table
  • Rows returned to the result set that qualify the driving WHERE clause
  • Cost=outer access + (inner table access * outer cardinality)

Hotka Page 64 Nested

Merge Join

The driving table is the first one, but in this case, it doesn’t really matter which table is first and second.  Both tables are sorted.  Then the returning rows are merged together and passed to the next step on the explain plan tree.

  • Both result sets should be larger in size
  • Doesn’t matter which one is first
  • Cost=outer access + inner access +sort costs

Hotka Page 64 Merge

Hash Join

The driving table should be the smaller.  Oracle loads the driving table into a hash table first, then looks up each row in this hash table in the inner table.  So, the smaller table should be first, or the driving table.

  • Smaller result set should be the driving table
  • Larger result set should be the inner table
  • Cost=inner costs + (outer cost*inner cardinality/hash partitions)

Hotka Page 64 Hash

NOTE

Make sure both sides of the join condition have the same data type, not just the same data type family.  Oracle WILL make them the same data type behind the scenes and it will be difficult to get Oracle to use an obvious index perhaps.

TIP

You add the function to make the data types the same.  Then you are in control of where the function falls in the code, not Oracle.  You can then manipulate the code to possibly use an index.

?

?

Related Posts

Trackbacks

Leave a Reply