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 ASC
The following predicates could be used to limit the range of a scan that uses index IX1:
orwhere 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 thewhere name = :hv1 and dept = :hv2 and salary = :hv4 and years = :hv5
name = :hv1
anddept = :hv2
predicates, the other predicates can be evaluated against the remaining index key columns. - Consider an index that was created using the ALLOW REVERSE SCANS
option:
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
- Strict
inequality predicates
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:
- >= and <=
- BETWEEN
- LIKE
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.where name = :hv1 and dept >= :hv2 and dept <= :hv3 and mgr <= :hv4
Suppose that
: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 values
or any expression that evaluates to a constant. - Indexes with random ordering cannot be used to order data.
where
name = 'JONES' and
dept = 'D93'
order by mgr
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 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.
Jump scans
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
IX1
composite index defined earlier in this topic:
This query contains an index gap: on thewhere name = :hv1 and dept = :hv2 and mgr = :hv3 and years = IS NOT NULL
SALARY
key part of the composite index (this is assuming that the access plan contains an index scan on the composite index). TheSALARY
column cannot be included as a start-stop predicate. TheSALARY
column 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
IX1
composite index defined earlier in this topic: where
name = :hv1 and
dept = :hv2 and
mgr = :hv3 and
salary < :hv4 and
years = :hv5
This query contains an index gap on the SALARY
key
part of the composite index (this is assuming that the access plan
contains an index scan on the composite index). Since the SALARY
column
in the query is not an equality predicate, start-stop values cannot
be generated for this column. The SALARY
key 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.