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.