5.4.1 Evaluation method when B-tree indexes are used

(1) About the range search condition

A condition for specifying a search range when B-tree indexes are used is called a range search condition. This includes mainly the following conditions:

• =, not-equal sign, IS NULL, LIKE predicate leading match (literal specification), LIKE predicate dynamic parameter specification, IN predicate^#, BETWEEN predicate, quantified predicate (=ANY, =SOME)

  #

  An IN predicate (that is not a table subquery specification) specified for an inner table of a nested loop join might not be a search condition.

The following figure shows an example of the evaluation method based on a range search condition.

Figure 5‒4: Example of evaluation method based on a range search condition

Explanation:

In this example, the range search condition is 50 ≤ C1 ≤ 100. HADB accesses the data pages that contain the rows that satisfy the range search condition.

To Page Top

(2) About the key condition

A condition that can be evaluated only by B-tree-indexed columns is called a key condition. A key condition cannot be used to narrow down the search range when B-tree indexes are used, as a range search condition can do. However, a key condition can reduce the number of times a data page is referenced because the key condition can be evaluated by using only B-tree index pages, thereby improving search performance.

The following figure shows an example of the evaluation method based on a key condition.

Figure 5‒5: Example of evaluation method based on a key condition

Explanation:

In this example, the conditions are as follows:

• Range search condition: 40 ≤ C1 ≤ 60

• Key condition: C2 IN(1,2)

HADB uses the range search condition to narrow down the search range and accesses only those data pages that contain the rows that satisfy the key condition.

To Page Top