Background documentationRange Condition Locate this document in the navigation structure

 

A range condition is a search condition with a comparison predicate or a BETWEEN predicate.

The SQL Optimizer can only evaluate range conditions that satisfy the following restrictions:

  • In the range condition, only the comparison operators < | <= | => | >

    and the BETWEEN operator are used.

  • The range condition has one of the following forms:

    <column_spec> < < | <= | => | > > <extended_value_spec>

    <column_spec> BETWEEN <extended_value_spec> AND <extended_value_spec>

For the search strategy, it is not important whether the range is limited with a BETWEEN operator or with a combination of comparison operators <= and =>. The database system converts conditions of the following forms, if possible, into an expression without NOT with a correspondingly negated operator:

... 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.

Example

The following example uses the demo database DEMODB with the complete demo data in the schema HOTEL (see Concepts of the Database System, Objects in the Schema HOTEL

Example Example

EXPLAIN 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 CUSTOMER table: cno

  • Indexes via qualified columns: FULL_NAME_INDEX (name,firstname)

  • Search strategy used: TABLE SCAN

End of the example.

In this example, the database system could use the index FULL_NAME_INDEX (name,firstname) to access data for example because, for the NAME and FIRSTNAME columns, a range condition (BETWEEN) and an equality condition was specified. The database system does not choose this search strategy however because, in the case of an index access, it would have to access the base table and, above a certain range size, this would incur excessive read costs.

Instead, the database system chooses TABLE SCAN as the search strategy because 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 can find additional simple example SQL statements in the SQL Tutorial.

More Information