Backward index scan is possible with DB2 backward scrolling cursors if the ORDER BY clause in the query requests an order in the exact opposite sequence of key columns in the index. This means that one index can be used to avoid sorting for both forward and backward scrolling cursors. While the actual scan process may be no more efficient than having both a forward and backward index, it can create an opportunity to eliminate that extra index that supports the backward scroll. This is important in the overall performance scheme because indexes require time and money to maintain. The fewer indexes there are to maintain, the more efficient overall performance can be achieved.
The soundex function was introduced with DB2 9 and provides built-in name search assistance. The soundex algorithm assists with the filing and searching of information based on a name. It converts a name into a four-character code. The first character is the first letter of the name. The remaining four characters are all numeric based on the remainder of the name. Vowels are usually ignored, as are repetitive consonants, but there are exceptions.
We can use the soundex algorithm for more complete searches when names are misspelled. In addition, the soundex value can be stored in the database and an index created on that stored column, or an index can be created on the soundex function itself. This will enable index matching access for executions of the soundex function.
Index on Expression
Another important feature built into DB2 9 enables you to create an index key based on an expression. This feature lets you compensate for predicates built on column expressions—normally stage-two predicates that can’t use an index. This is potentially a significant performance improvement for existing predicates and future designs. In the CREATE INDEX statement, an index is actually built on the result of a function, such as the soundex function, for every row in the table. So, the first key value of the index is the soundex result. A query can then use the soundex function as it is coded in the index of the table to search for someone with a specific soundex value. Now the predicate can use an index.
Hash access can be used to optimize data access to tables for commonly issued equals predicates that access a single row in a table. The hash access supports direct access to a row for these types of retrievals without using an index. Hash access can also be used to force uniqueness within a table without an index. Hash access for a table is enabled by specifying the ORGANIZED BY HASH clause of the CREATE TABLE or ALTER TABLE STATEMENT. This clause specifies column names be used as part of the hash key, and the hash key is used to determine the physical location of rows in the table. Hash access can provide a dramatic performance improvement for simple equals predicates on such things as primary keys. Avoiding a traverse of the index tree can reduce both elapsed time and CPU time for these types of searches. Be aware, however, that the hash space can as much as double the storage requirements for a table, and doesn’t necessarily guarantee you can eliminate an index.
In DB2 10, system-period temporal tables automate the storage of current and history data in separate tables. With this built-in temporal design, you build a current table and a history table, then tell DB2 that the two tables are related in that manner. DB2 then moves data from the current table to the history table whenever there’s a change to data in the current table.