Range Condition 

Range conditions are search conditions with a comparison predicate or a BETWEEN predicate. For the SQL Optimizer, the following restrictions apply to range conditions:

●     The only valid operators are the relational operators < | <= | => | > and the operator BETWEEN.

Range conditions have one of the following formats:
<column_spec> < < | <= | => | > > <extended_value_spec>

or
<column_spec> BETWEEN <extended_value_spec> AND <extended_value_spec>

To determine an optimal search strategy, the SQL Optimizer can only evaluate range conditions that satisfy these prerequisites.

Whether the range is defined using a BETWEEN operator or whether it is defined using a combination of the relational operators <= and => is irrelevant to the search strategy.

Conditions with the following format are converted to an expression without NOT and with the appropriate negated operator, if possible:

...NOT (<column_spec> < < | <= | => | > > <extended_value_spec>)

...NOT (<column_spec> NOT BETWEEN <extended_value_spec> AND <extended_value_spec>)

The SQL Optimizer then continues to process the modified condition.

Examples

The examples used to explain the SQL Optimizer are based on the demo database DEMODB and its complete demo data in the schema HOTEL.

Concepts of the Database System, Objects in the Schema HOTEL

SELECT * FROM hotel.customer
  WHERE name BETWEEN 'B' AND 'P' AND firstname = 'Greg'

Qualification: name BETWEEN 'B' AND 'P' And firstname = 'Greg'
Primary key of the table CUSTOMER: cno
Indexes of qualified columns: FULL_NAME_INDEX (name, firstname)
Search strategy used: TABLE SCAN

The index FULL_NAME_INDEX (name, firstname) could be used because a range (BETWEEN) and an equality condition were specified for the columns NAME and FIRSTNAME. A TABLE SCAN is selected here because with an index access, the base table must also be accessed afterwards. Thus, when a certain range size is exceeded, the reading costs are too high. In a TABLE SCAN, there are no read costs for the index.

Result of the EXPLAIN Statement

TABLENAME	COLUMN_OR_INDEX	STRATEGY	PAGECOUNT
CUSTOMER	FULL_NAME_INDEX	RANGE CONDITION FOR INDEX	40
		RESULT IS NOT COPIED, COSTVALUE IS	40

You will find additional simple example SQL statements in the SQL Tutorial.

See also:

EXPLAIN Statement

SQL Reference Manual, Comparison Predicate (comparison_predicate), BETWEEN Predicate (between_predicate)