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.
INDEX IX1: NAME ASC,
DEPT ASC,
MGR DESC,
SALARY DESC,
YEARS ASC
where
name = :hv1 and
dept = :hv2
or 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.
where
name = :hv1 and
dept = :hv2 and
salary = :hv4 and
years = :hv5
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 name
= :hv1 and dept = :hv2 predicates, the other
predicates can be evaluated against the remaining index key columns. create index iname on tname (cname desc) 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.The strict inequality operators that are used for range-limiting predicates are greater than (>) and less than (<).
where
name = :hv1 and
dept > :hv2 and
dept < :hv3 and
mgr < :hv4
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
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.
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.
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.
where
name = :hv1 and
dept = :hv2 and
mgr = :hv3 and
years = IS NOT NULL
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). The SALARY column
cannot be included as a start-stop predicate. The SALARY column
is an example of an unconstrained index gap. 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.