Understanding SQL execution
Now that we have a general understanding of what
happens to an SQL statement inside library
cache, it's time to take a closer look at how
optimizer SQL within a relational database.
Some of the more sophisticated relational
databases such as Oracle and DB2 have
specialized techniques that you can use in order
to make SQL statements fully reentrant. Many
database management systems also have an
automated query rewrite facility, where many
sub-optimal SQL query form can be detected and
dynamically re-written into a more optimal form.
This section explores techniques to reduce
excessive re-parsing for SQL statements, and
then takes a look at how you can view the actual
execution plan for specific SQL statements.
Once we are able to view the execution plan for
an SQL statement, the next step is to take a
look at the table and index access methods that
are available to service a query.
As we discussed in the last tutorial, the
fall-back position for the SQL optimizer is to
perform a full table scan, reading every row in
the table. Of course, it is far more efficient
for an SQL statement to use index access to get
the table rows, especially when the where clause
of the SQL query has highly restrictive clauses.
We are now ready to investigate the details of
how row data is accessed from SQL. We will
start by exploring the process of determining
the optimal table join order because this is one
of the most important decisions to the optimizer
must make. For example, an SQL query that joins
six tables together has six factorial (6!)
possible combinations of table joins. This is 6
x 5 x 4 x 3 x 2, or 720 possible ways that the
SQL optimizer can join these six tables
together. This can take a huge amount of time!
Oracle provides a hint called ORDERED to
allow us to manually specify the table join
order, and bypass the expense of determining the
optimal table join order.
Once the optimizer is determined the optimal
order in which to join the tables together, the
SQL optimizer must then decide upon the
appropriate method for joining the tables
together. The Oracle database offers several
table join methods. Oracle has a sort merge
join, a nested loop join, and a hash join
method, all of which serve the common purpose of
joining to tables together, but with radically
different internal mechanisms and external
performance.
