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:
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>)
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.
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 |
Displaying the Search Strategies Used by SQL Statements (EXPLAIN)
SQL Reference Manual, Comparison Predicate (comparison_predicate), BETWEEN Predicate (between_predicate)
You can find additional simple example SQL statements in the SQL Tutorial.