The Optimizer Takes Its Own Advice

DB2 UDB for i5/OS offers new feature, Index Advised

DB2 UDB for i5/OS offers new feature, Index Advised

Having a good indexing strategy is imperative to achieving high query performance on any database platform. A comprehensive and efficient indexing strategy can mean the difference between queries that complete in seconds rather than minutes or hours. Implementing such a strategy is an important task. DB2* Universal Database* (UDB) for i5/OS* wants to lend a hand in the form of Index Advised, which is a feature that enumerates definitions for nonexistent indexes with a high potential for improving a query's performance. Not only does Index Advised help guide the creation of permanent indexes, but it's also the basis for creating a new flavor of temporary index in V5R4 called a Maintained Temporary Index (MTI). An understanding of how Index Advised works, how it's used to create MTIs and which tools are available to analyze this advice is essential for anyone looking to create a highly effective indexing strategy.

Basic Building Blocks

Index Advised itemizes the set of index definitions that the optimizer deems necessary to achieve better query performance. Because this feature is built directly into the optimizer, all SQL queries are automatically analyzed for new indexing opportunities based on the rules outlined in the "Indexing and Statistics Strategy" whitepaper (see the "Online Tools" sidebar). In general, Index Advised first looks for local selection equal predicates (e.g., table.column=constant). (Note: Index Advised can advise radix indexes or encoded vector indexes, or EVIs, to handle selection.) For joining, the optimizer adds join columns to the advised column list following the columns from local selection equal predicates. If present, columns from in-list predicates or a column from a single inequality local selection predicate (e.g., >, <, != etc.) are appended to the list. These columns appear last because the number of records they select can vary widely. Also, whereas columns against equal predicates can be moved around in their position with other equal predicate columns, non-equal selection columns don't provide this flexibility. For grouping or ordering, Index Advised may include the grouping and ordering columns after the local selection equal predicates, but this is only true when all of the columns in the "group by" or "order by" list come from the same table. Additionally, the optimizer can recommend using a multiple-key column EVI to handle star-schema-type joins or simple grouping.

Meet the MTIs

While Index Advised is a highly effective end-user tool for improving query performance, it's also the basis for the creation of another new tool in the Structured Query Engine (SQE) optimizer's toolbox - the MTI. An MTI is a temporary radix index that the SQE optimizer can create to improve a query plan's performance. It has an advantage over other temporary result sets created by the optimizer: it's maintained. Like a permanent radix index, an MTI is updated as the underlying table is updated. Therefore, the cost of maintaining an MTI is similar to the cost of maintaining a permanent radix index.

The optimizer creates an MTI for a query plan for two main reasons. One, an index is functionally necessary to implement a query when a temporary result set isn't allowed. A query running under a sensitive cursor is one example where a non-maintained temporary result set can't be used to implement a query. The other reason the optimizer can choose to create an MTI is that the query will perform better with an index. While the Classic Query Engine (CQE) has the ability to create a temporary index, its usage is restricted to a single job and single query only. SQE's MTIs, on the other hand, can be shared across queries and jobs just like a permanent index. SQE automatically controls the creation, sharing and deletion of MTIs. Creation of an MTI occurs when the optimizer determines that a query's performance can be enhanced sufficiently by using the MTI, thus justifying the MTIs create cost. An MTI is deleted when the last plan in the system plan cache referencing it is removed (e.g., IPL) or when a permanent index is created that covers the same columns as the MTI. To avoid incurring MTI create costs after an IPL, consider creating permanent indexes for regularly used MTIs. MTI usage information can be viewed using iSeries* Navigator or the Database Monitor.

Now that the SQE optimizer has the ability to create maintained, shareable indexes, it relies on feedback from Index Advised to create those MTIs. When Index Advised recommends an index that may help a query perform better, the optimizer considers the effect on the query performance of creating that index. The cost of creating the index is considered, but it's discounted the more times a query runs. So, the first time a given query runs, creating an MTI based on information from Index Advised may not be justified. Other techniques such as hash join and table scan are typically more appropriate for a query that has only run once. If the optimizer sees that a query runs frequently, it's more likely to create the advised MTI. The justification is based on the fact that the one-time cost of creating the MTI will be more than offset by the runtime savings in subsequent query runs of this query and others. To garner this "historical use information," the optimizer interrogates the system plan cache.

The QAQQINI file gives users the ability to override some of the initialization settings used by the optimizer during optimization. Two QAQQINI parameters affect MTI creation. One is the ALLOW_TEMPORARY_INDEXES parameter, which has two values: *YES and *ONLY_REQUIRED. The default value for this parameter is *YES. When *YES is specified, the optimizer is free to create MTIs as it sees fit. When the *ONLY_REQUIRED value is specified, the optimizer will create MTIs only when they're functionally necessary for sensitive (live data) queries. By specifying *ONLY_REQUIRED, indexes recommended by Index Advised aren't created.

Visual Explain provides users with a Highlight Index Advised feature that highlights any icon with Index Advised information.

Rob Downer is a senior software engineer at IBM. Rob can be reached at

Shantan Kethireddy is a member of the IBM SQL Query Engine team in Rochester, Minn. Shantan can be reached at

comments powered by Disqus



2017 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

It’s Technical, Dear Watson

The “Jeopardy!” playing computer’s feeds and speeds

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters