Background documentationSearch Strategies for Key Column Search Conditions Locate this document in the navigation structure

 

If an SQL statement contains a search condition for key columns, the SQL optimizer can use the following search strategies.

Search Strategies for Key Column Search Conditions

Search Condition

Search Strategy

Equality Condition for key columns

The database system accesses those rows for which keys are contained in the inversion list.

IN Condition for key columns

The database system accesses those rows for which keys are contained in the inversion lists.

Range Condition with only one condition (<, ≤, ≥, > ) for one of the two range limits (upper or lower limit)

The database system accesses those rows for which keys are contained in the inversion lists defined by the range.

Range condition for which both range limits are specified

For the selection of the search strategy, it is not important whether the condition was specified by a BETWEEN operator or by two conditions linked by an AND ( ≤ or ≥) for the same column.

The database system accesses those rows for which keys are contained in the inversion lists defined by the range.

Example

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

Equality Condition for a Key Column

EXPLAIN SELECT name

  FROM hotel.customer

    WHERE cno = 7020

  • Qualification: cno = 7020

  • Primary key of CUSTOMER table: cno

  • Indexes via qualified columns: None

  • Search strategy: EQUAL CONDITION FOR KEY

Result of the EXPLAIN Statement

TABLENAME

COLUMN_OR_INDEX

STRATEGY

PAGECOUNT

CUSTOMER

EQUAL CONDITION FOR KEY

40

CNO

(USED KEY COLUMN)

RESULT IS NOT COPIED, COSTVALUE IS

1

Because the primary key was fully qualified in the search condition, the database system accesses the data record directly.

Range Condition for a Key Column

EXPLAIN SELECT zip, name

  FROM hotel.city

    WHERE zip BETWEEN '10000' AND '20000'

  • Qualification: BETWEEN '10000' AND '20000'

  • Primary key of CUSTOMER table: zip

  • Indexes via qualified columns: None

  • Search strategy: RANGE CONDITION FOR KEY

Result of the EXPLAIN Statement

TABLENAME

COLUMN_OR_INDEX

STRATEGY

PAGECOUNT

CITY

RANGE CONDITION FOR KEY

11

ZIP

(USED KEY COLUMN)

RESULT IS NOT COPIED, COSTVALUE IS

1

More Information

Analyzing Search Strategies Using the EXPLAIN Statement

Search Strategies

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