Temporary Indexed List scan and Index Merge

A temporary indexed list scan operation is similar to the index scan operation that is performed upon the permanent radix index. It is still used to retrieve the rows from a table in a keyed sequence; however, the temporary indexed list object must first be created. This operation is often performed in conjunction with an index merge operation. However, for some distinct and grouping queries, the temporary indexed list scan may be used without an associated index merge operation.

This access method is used when there is non-equal selection and is used in conjunction with an existing permanent radix index. A distinct index scan or a distinct index probe operation is performed against the index to get a unique set of rows. The rows are then inserted into the temporary indexed list to provide the appropriate ordering.

In general, for a radix index to be eligible for temporary list scan and Index Merge Ordering, the index should be created as follows:

Any columns with equal predicates that are not in the ORDER BY list should be first keys in the index.
Followed by any columns for non-equal predicates that are not in the ORDER BY list. The columns should be arranged so the most selective predicate column is the first column following the equal predicate columns.
Followed by the ORDER BY columns in the same order as the ORDER BY clause of the query and with the same ASC or DESC attribute.
Optionally, at the end you may include other selection columns or other selected columns for Index Only Access.

A temporary sorted list is an internal data structure and can only be created by the database manager.

Table 1. Temporary indexed list scan
Data access method	Temporary indexed list scan
Description	Sequentially scan and process all the keys associated with the temporary indexed list.
Advantages	Provides an alternate implementation to order data when the query contains non-equal where selection. Returns the rows back in a sequence based upon the keys of the index. Provides better paging characteristics than a sorted list scan in low memory environments. Provides better performance characteristics than a Sorted List Scan when only a partial answer set is fetched.
Considerations	Used to process ordering, grouping or distinct processing for a single table query.
Likely to be used	When the use of temporary results is allowed by the query environmental parameter (ALWCPYDTA). When the data is required to be ordered or grouped based upon a column or columns and the query contains non-equal where selection. The query is being optimized for FIRST I/O. • The number of distinct values is low.
Example SQL statement	`CREATE INDEX INDEX1 ON EMPLOYEE(SALARY, WORKDEPT)` `SELECT DISTINCT WORKDEPT FROM EMPLOYEE WHERE SALARY > 30000 OPTIMIZE FOR 30 ROWS`
Database Monitor and Plan Cache record indicating use	QQRID 3001 where QQRCOD = ‘I7’
SMP parallel enabled	No
Also referred to as
Visual Explain icon

Using the example above, the optimizer chose to create a temporary indexed list of distinct WORKDEPT values from index INDEX1. The selection SALARY > 3000 was applied to the index probe distinct.

Index Merge:

An index merge operation is used to provide ordering in conjunction with a temporary indexed list scan and an index probe distinct or distinct index.

The sequenced rows can be used by the optimizer to satisfy a portion of the query request (such as ordering or grouping).

Table 2. Index Merge
Data access method	Index Merge
Description	Sequentially scan and process all the keys associated with the temporary indexed list.
Advantages	Potential to extract all the data from the index key values, thus eliminating the need for a Table Probe. Returns the rows back in a sequence based upon the keys of the index.
Considerations	Used to process ordering, grouping or distinct processing for a single table query.
Likely to be used	When the use of temporary results is allowed by the query environmental parameter (ALWCPYDTA). When the data is required to be ordered or grouped based upon a column or columns and the query contains non-equal where selection. The query is being optimized for FIRST I/O. The number of distinct values is low.
Example SQL statement	`CREATE INDEX IX1 ON Sales(Sales_date, Region, Sales_person)` `SELECT * FROM Sales WHERE Sales_date BETWEEN '1996-03-29' AND '1996-04-29' AND Region IN ('Quebec','Manitoba') ORDER BY Sales_person;`
Database Monitor and Plan Cache record indicating use	QQRID 3001 where QQRCOD = ‘I7’
SMP parallel enabled	No
Also referred to as
Visual Explain icon