DB2 also automatically supports querying data based on a timestamp. You can choose how to read the temporally related tables; you can read the current table for the current data and the history table for all historical queries. If you want to read data “as of” a given time, or time range, you can query the current table and specify a system time predicate in the query. DB2 will return the data from both the current and history tables relative to the time or period you requested. It does this by rewriting the query as a UNION ALL of a query against the current table and a query against the history table. While this approach may not be a high-performance solution, it relieves the application and DBA from a lot of extra work.
Improved In-List Matching
One of the optimization techniques available in DB2 10 is improved matching index access for multiple in-list predicates in a single query. In previous versions, if multiple in-lists were coded in a query, only one of them would be eligible for index matching. Now DB2 can build work files using the in-lists and then use those work files in a join to the target table.
Improved Pagination Optimization
DB2 10 also introduces range-list index scan. Search queries, especially those that use scrolling techniques to control pagination, can contain multiple OR predicates. In previous versions of DB2, multiple index access was used to avoid a non-matching index scan or table space scan. In DB2 10, a range-list index scan can be used; it will consume fewer RID list resources than multi-index access. DB2 can use a range-list index scan when a query meets these requirements:
- Every OR predicate refers to the same table.
- Every OR predicate has at least one matching predicate.
- Every OR predicate is mapped to the same index.
Search Query Variations
SQL advancements provide many choices for doing things such as complex searches. If we must search for multiple conditions or values, we can choose from several options.
The sample queries in Figure 1 search for two different names using three different techniques. The first query uses a compound predicate that might get multi-index access at best under DB2 9, or perhaps range-list index access under DB2 10. The second query uses a UNION ALL to perform the search for each search condition, which has a better chance of two-column index matching access, but executes two query blocks. The third query builds a search table using a common table expression and then uses relational division (a join) to perform the complex search. Each of these queries has merit; it’s best to code them all, EXPLAIN, and benchmark test each of them.
Searching on Multiple Ranges