5.1.3 About key scans

A key scan uses a B-tree index to evaluate the search conditions and retrieves from index pages the column values in the rows that satisfy the search conditions. This method can reduce the number of pages to be referenced because it retrieves column values directly from B-tree-indexed columns (keys).

A key scan is performed when a B-tree index is defined as an indexed column for all the columns specified in the SQL statement, and one of the following conditions is met:

• A B-tree index is defined in the table, and a search condition is specified that can effectively use the B-tree index.

• The B-tree index used for retrieval is specified in an index specification.

• A set function MIN or MAX is specified.^#

• SELECT DISTINCT is specified.^#

• UNION or UNION DISTINCT is specified.^#

• EXCEPT or EXCEPT DISTINCT is specified.^#

• INTERSECT or INTERSECT DISTINCT is specified.^#

• A quantified predicate with =ANY specification is specified.^#

• An IN predicate with a table subquery is specified.^#

#

The B-tree index is used only when HADB determines that the B-tree index can be used efficiently.

However, if any of the following conditions are satisfied, HADB might reference the data pages:

• A table in which rows have been added, updated, or deleted is being retrieved

• A column of the VARCHAR type that contains data ending with a single-byte space is being retrieved

• Data in the VARBINARY column whose binary data ends with X'00' is being referenced

The following figure shows an example of a key scan.

Figure 5‒3: Example of a key scan

Explanation:

This example uses B-tree index IDX_C1C2 to evaluate the search conditions. HADB does not access data pages because it retrieves the values of columns C1 and C2 directly from the index page.

Note

Key scans are not executed for text indexes.

To Page Top