Reviewing the Execution Plan

Reviewing the Execution Plan
============================

When tuning (or writing) a SQL statement in an OLTP environment, the goal is to
drive from the table that has the most selective filter. This means that there are fewer rows passed to the next step. If the next step is a join, then this means that fewer rows are joined. Check to see whether the access paths are optimal.
When examining the optimizer execution plan, look for the following:

■ The plan is such that the driving table has the best filter.
■ The join order in each step means that the fewest number of rows are being
returned to the next step (that is, the join order should reflect, where possible,
going to the best not-yet-used filters).
■ The join method is appropriate for the number of rows being returned. For
example, nested loop joins through indexes may not be optimal when many rows
are being returned.
■ Views are used efficiently. Look at the SELECT list to see whether access to the
view is necessary.
■ There are any unintentional Cartesian products (even with small tables).
■ Each table is being accessed efficiently:
Consider the predicates in the SQL statement and the number of rows in the table.
Look for suspicious activity, such as a full table scans on tables with large number
of rows, which have predicates in the where clause. Determine why an index is not
used for such a selective predicate.

A full table scan does not mean inefficiency. It might be more efficient to perform a full table scan on a small table, or to perform a full table scan to leverage a better join method (for example, hash_join) for the number of rows returned.

If any of these conditions are not optimal, then consider restructuring the SQL
statement or the indexes available on the tables.