Show TOC

Range ConditionLocate this document in the navigation structure

Use

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

Prerequisites

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

  • Only the comparison operators < | ≤ | ≥ | > or 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 ≤ | ≥.

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>)
			
Note

The SQL optimizer can only transfer queries if the QueryRewrite function is enabled (special database parameter EnableQueryRewrite).

For more information, see Database Administration, Special Database Parameters.

Example

Note that depending on the amount of data, the system might use different strategies.

Range Condition

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: RANGE CONDITION FOR INDEX

TABLENAME

COLUMN_OR_INDEX

STRATEGY

PAGECOUNT

CUSTOMER

FULL_NAME_INDEX

RANGE CONDITION FOR INDEX

40

   

RESULT IS NOT COPIED, COSTVALUE IS

4 0