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)
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
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)
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.
?
?
[…] Oracle Explain Plans: Driving Tables and Table Joins. […]