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.

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:

    Syntax Syntax

    1. <column_spec> <|≤|≥|> <extended_value_spec>
<column_spec> BETWEEN <extended_value_spec> AND <extended_value_spec>
    End of the code.

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:

Syntax Syntax

  1. ... NOT (<column_spec> <|≤|≥|> <extended_value_spec>)
... NOT (<column_spec> NOT BETWEEN <extended_value_spec> AND <extended_value_spec>)
End of the code.

Note Note

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

More information: Database Administration, Special Database Parameters

End of the note.

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

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

More Information

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