Note: This is the second in a two-part series. The first part ran in the
November 2006 issue of the magazine.
The intent of this second article installment is to help make the SQL tuning
process more intuitive for you without requiring years of study and
practice.
Let's begin with a brief review of the key points from my previous article.
Intuitive tuning is driven by the simple concept that, when a query takes more
CPU or elapsed time than expected, it's typically doing more I/O. The focus of
query tuning is to minimize I/O by exploiting the DB2* optimizer, which is done
by defining new indexes.
To enable the DB2 optimizer to determine a more efficient data-access path,
the following tuning techniques were discussed:
Run the RUNSTATS utility - This utility gathers summary information about
the data characteristics in tables and associated indexes. When this information
is accurate and up-to-date, the DB2 optimizer is able to choose an efficient
data path.
Put all of the query predicates in an index - Not doing so causes DB2 to
perform I/O to retrieve data for further predicate qualification. That I/O can
be wasted if the subsequent predicate test fails.
Attempt to avoid a DB2 sort by placing the sort columns in an index - An
index can be thought of as a sequential file. The fact the index is sequenced
can sometimes be exploited by the DB2 optimizer to avoid a sort for an Order By,
Group By, Select Distinct or Union (note that Union with the keyword "All"
doesn't require a sort). This point is developed later.
Evaluate putting other columns in the index (columns that are neither
predicates nor used for sorting) - When all of the columns referenced in a table
are in an index, DB2 can retrieve the data from the index alone without
additional I/O to the table. This is referred to as index-only processing. You
should consider this when there are few additional columns and when the query in
question is frequently executed.
A word of caution: indexes aren't free. The SQL insert, delete and update
cause maintenance to the index when an indexed column is modified.
This article will demonstrate these simple techniques against more complex
queries while continuing to maintain an appeal to the intuition.
A 'view' doesn't contain data. In general, a view presents the appearance of a table to the application program, when in fact it can be any or all of the rows from one or more tables.
