DB2 Explains Itself: A Roadmap to Faster Query Runtime-4 : Page 4
by Greg Nash, DB2 Pro
Nov 1, 2001
Page 4 of 4
Reducing the Cost Now that you can read the price tags, it's time to reduce the cost. Here are some guidelines:
1.) Better-informed decisions. DB2's optimizer must be well informed to make the best decisions.
Use the RUNSTATS to update the statistics. If your data has grown, or you've added or removed indexes since the last RUNSTATS, DB2's estimates will be inaccurate.
If your tables aren't fully populated, the runtime statistics will be inappropriate. Under such conditions you'll often find tablescans in your plans, in spite of sensible indexes. You can tell the optimizer to anticipate extra data by marking certain tables as VOLATILE. (Use the Control Center, or the ALTER TABLE command).
2.) Don't buy junk. It's a simple rule - don't buy what you don't need. This includes:
Don't use "SELECT * " when you don't need all the columns.
Use a WHERE clause when possible. A common beginner's mistake is to use HAVING instead of WHERE. "HAVING" is like analyzing every item in a supermarket, then discarding those you don't want. Sometimes that's what you need, but it's quicker if you can say "I only want to look at the breakfast cereals."
Look carefully at your joins. The sooner (in the plan) you can narrow down the records you want, the less work it will be. Compare Listing 3 and Listing 4. They represent the same query, but by re-ordering the joins, the cost has been halved. (The reason for the cost difference relates to how many rows are in each table).
3.) Keep an index handy. Many columns can be used in an index, and the order of the columns is significant. You might want to use more than one index.
DB2 provides a clever tool, the SQL Advise Facility. It's available from the Control Center (Indexes - Create - Index using wizard), and from the command line as db2advis. The advisor takes a query, examines the generated plan, attempts to identify additional indexes that may help, and re-evaluates the cost with and without each of the indexes. The end result is a list of potentially helpful indexes, with estimates of their size and helpfulness.
You can also INCLUDE unsorted data in an index, to completely avoid fetching from the table in some instances.