Data access through index scans
An index scan occurs when the database manager accesses an index to narrow the set of qualifying rows (by scanning the rows in a specified range of the index) before accessing the base table; to order the output; or to retrieve the requested column data directly (index-only access).
When scanning the rows in a specified range of the index, the index scan range (the start and stop points of the scan) is determined by the values in the query against which index columns are being compared. In the case of index-only access, because all of the requested data is in the index, the indexed table does not need to be accessed.
If indexes are created with the ALLOW REVERSE SCANS option, scans can also be performed in a direction that is opposite to that with which they were defined.
- When the table is small.
- When the index-clustering ratio is low.
- When the query requires most of the table rows.
- Where extra sorts are required when a partitioned index is used. A partitioned index cannot preserve the order in certain cases.
- Where extra sorts are required when an index with random ordering is used.
Index scans to limit a range
- Tests for IS NULL or IS NOT NULL
- Tests for strict and inclusive inequality
- Tests for equality against a constant, a host variable, an expression that evaluates to a constant, or a keyword
- Tests for equality against a basic subquery, which is a subquery that does not contain ANY, ALL, or SOME; this subquery must not have a correlated column reference to its immediate parent query block (that is, the select for which this subquery is a subselect).
- Consider an index with the following definition:
INDEX IX1: NAME ASC, DEPT ASC, MGR DESC, SALARY DESC, YEARS ASCThe following predicates could be used to limit the range of a scan that uses index IX1:
where name = :hv1 and dept = :hv2
where mgr = :hv1 and name = :hv2 and dept = :hv3
The second WHERE clause demonstrates that the predicates do not have to be specified in the order in which the key columns appear in the index. Although the examples use host variables, other variables, such as parameter markers, expressions, or constants, could be used instead.In the following WHERE clause, only the predicates that reference NAME and DEPT would be used for limiting the range of the index scan:
Because there is a key column (MGR) separating these columns from the last two index key columns, the ordering would be off. However, after the range is determined by the
where name = :hv1 and dept = :hv2 and salary = :hv4 and years = :hv5
name = :hv1and
dept = :hv2predicates, the other predicates can be evaluated against the remaining index key columns.
- Consider an index that was created using the ALLOW REVERSE SCANS
In this case, the index (INAME) is based on descending values in the CNAME column. Although the index is defined for scans running in descending order, a scan can be done in ascending order. Use of the index is controlled by the optimizer when creating and considering access plans.
create index iname on tname (cname desc) allow reverse scans
Index scans to test inequality
The strict inequality operators that are used for range-limiting predicates are greater than (>) and less than (<).Only one column with strict inequality predicates is considered for limiting the range of an index scan. In the following example, predicates on the NAME and DEPT columns can be used to limit the range, but the predicate on the MGR column cannot be used for that purpose.
However, in access plans that use jump scans, multiple columns with strict inequality predicates can be considered for limiting the range of an index scan. In this example (assuming the optimizer chooses an access plan with a jump scan), the strict inequality predicates on the DEPT, and MGR columns can be used to limit the range. While this example focuses on strict inequality predicates, note that the equality predicate on the NAME column is also used to limit the range.
where name = :hv1 and dept > :hv2 and dept < :hv3 and mgr < :hv4
- Inclusive inequality predicatesThe inclusive inequality operators that are used for range-limiting predicates are:
Multiple columns with inclusive inequality predicates can be considered for limiting the range of an index scan or a jump scan. In the following example, all of the predicates can be used to limit the range.
- >= and <=
where name = :hv1 and dept >= :hv2 and dept <= :hv3 and mgr <= :hv4
:hv2 = 404,
:hv3 = 406, and
:hv4 = 12345. The database manager will scan the index for departments 404 and 405, but it will stop scanning department 406 when it reaches the first manager whose employee number (MGR column) is greater than 12345.
Index scans to order data
= ANYsubquery, a
> ALLsubquery, a
< ALLsubquery, INTERSECT or EXCEPT, and UNION. Exceptions to this are as follows:
- If the index is partitioned, it can be used to order the data only if the index key columns are prefixed by the table-partitioning key columns, or if partition elimination eliminations all but one partition.
- Ordering columns can be different from the first index key columns
when index key columns are tested for equality against
constant valuesor any expression that evaluates to a constant.
- Indexes with random ordering cannot be used to order data.
For this query, the index might be used to order the rows, because NAME and DEPT will always be the same values and will therefore be ordered. That is, the preceding WHERE and ORDER BY clauses are equivalent to:
where name = 'JONES' and dept = 'D93' order by mgr
where name = 'JONES' and dept = 'D93' order by name, dept, mgr
UNIQUE INDEX IX0: PROJNO ASC select projno, projname, deptno from project order by projno, projname
Additional ordering on the PROJNAME column is not required, because the IX0 index ensures that PROJNO is unique: There is only one PROJNAME value for each PROJNO value.
Queries against tables with composite (multi-column) indexes present a particular challenge when designing indexes for tables. Ideally, a query's predicates are consistent with a table's composite index. This would mean that each predicate could be used as a start-stop key, which would, in turn, reduce the scope of the index needing to be searched. When a query contains predicates that are inconsistent with a composite index, this is known as an index gap. As such, index gaps are a characteristic of how a query measures up to a table's indexes.
- Unconstrained index gap
- Consider the following query against a table with the
IX1composite index defined earlier in this topic:
This query contains an index gap: on the
where name = :hv1 and dept = :hv2 and mgr = :hv3 and years = IS NOT NULL
SALARYkey part of the composite index (this is assuming that the access plan contains an index scan on the composite index). The
SALARYcolumn cannot be included as a start-stop predicate. The
SALARYcolumn is an example of an unconstrained index gap.Note: For some queries it can be difficult to assess whether or not there are index gaps. Use the EXPLAIN output to determine if index gaps are present.
- Constrained index gap
IX1composite index defined earlier in this topic:
This query contains an index gap on the
where name = :hv1 and dept = :hv2 and mgr = :hv3 and salary < :hv4 and years = :hv5
SALARYkey part of the composite index (this is assuming that the access plan contains an index scan on the composite index). Since the
SALARYcolumn in the query is not an equality predicate, start-stop values cannot be generated for this column. The
SALARYkey part represents a constrained index gap.
To avoid poor performance in queries with index gaps, the optimizer can perform a jump scan operation. In a jump scan operation, the index manager identifies qualifying keys for small sections of a composite index where there are gaps, and fills these gaps with these qualifying keys. The end result is that the index manager skips over parts of the index that will not yield any results.
- Jump scan restrictions
- For queries being issued where you expect a jump scan, verify that the target table has an appropriate composite index and that the query predicates introduce an index gap. The Db2 optimizer will not create plans with jump scans if there are no index gaps.
- Jump scans do not scan the following types of indexes:
- range-clustered table indexes
- extended indexes (for example, spatial indexes)
- XML indexes
- text indexes (for Text Search)
- With jump scans, a gap column with an IN-List predicate might be treated as an unconstrained gap column. Also, in databases with Unicode Collation Algorithm (UCA) collation, jump scans might treat LIKE predicates with host variables or parameter markers as unconstrained gaps.