The reason we care whether our queries contain Boolean term predicates is that DB2 can only consider Boolean term predicates for efficient single index access. If your query contains no Boolean term predicates, then the best access you can get is either non-matching index access (usually to avoid a sort) or multi-index access.
There’s a simple solution to inefficient index utilization for scrolling cursors—add a Boolean term predicate! Figures 4 and 5 show two examples of queries that produce the same result with both non-Boolean term and Boolean term predicates. Often, you may be adding a redundant predicate, but the improved index access is worth it. Figure 4 shows our query from Figure 3 with a redundant Boolean term predicate, “AND EMPNO >= ?”, added. This predicate provides no additional filtering because the query already contains simple predicates that filter for EMPNO equal or EMPNO greater than the input value. However, because it is a Boolean term, DB2 can use the index defined on EMPNO for potentially faster query performance. Figure 5 shows a batch cursor operating on a range of a combination of fields using Boolean term predicates for matching index access of one column. Since the predicates are all connected by “AND,” logically a SEG value outside the range of input values will eliminate rows without the need for DB2 to check the SUBSEG value. So, DB2 can use an index on SEG for potentially faster query response. A future release of DB2 may be able to automatically do this for us, but for now (as of DB2 9), we must do it ourselves.
Choices for Multiple Searches
Often, search operations must handle multiple combinations of input. This is common in situations such as name reversals (typing in the last name where the first name is supposed to be and vice versa). The flexibility of the SQL language gives us many choices for these types of searches. The query in Figure 6 obtains the data for the first combination of fields, and then uses a during join predicate to search on the second combination of fields only when the first returns no result. DB2 will execute the second join only when the first finds nothing. This is an efficient way to perform an optional search.
The three queries in Figure 7 perform exactly the same function. The first relies on the potential for multi-index access. The second uses a UNION to run both searches as separate queries for the potential for full index access on each separate sub-select. The third uses a common table expression to build a table containing the search criteria in multiple columns and then join that table to the table being searched. This could boost index access for queries that search based on multiple optional columns. Default values could be built into the search criteria for potential full index access on optional search fields.
Which one of these techniques illustrated in Figure 7 is best for you? You must find out for yourself. This means running EXPLAIN against each of the statements as well as benchmarks to determine the actual impact of the query under simulated realistic conditions. This may seem like a lot of work (it usually isn’t), but the response time and CPU benefits last for years.