Background documentationSearch Conditions for Indexed Columns Locate this document in the navigation structure

 

If an SQL statement contains a search condition for indexed columns, SQL Optimizer can use the following search strategies:

Search Conditions for Indexed Columns: Overview

Search Condition

Search Strategy of SQL Optimizer

Equality condition for indexed columns

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

IN condition for indexed columns

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

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

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

Range condition for which both range limits are specified.

For 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 inversion lists defined by the range.

With SQL statements that contain equality conditions, IN conditions or range conditions, the database system does not need to access table rows since all required values are already contained in the inversion list(s).

Example

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

Equality condition for an indexed column

Example Example

EXPLAIN SELECT *

  FROM hotel.customer

    WHERE name = 'Wagner'

  • Qualification: name = 'Wagner'

  • Primary key of CUSTOMER table: cno

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

  • Search strategy: RANGE CONDITION FOR INDEX

End of the example.

Since the equality condition has been specified for the first index column only, the database system has to use the RANGE CONDITION strategy for this SQL statement. Several entries with the value Wagner may exist in the index.

The database system sets the start key when the first data record meets the equality condition in the first index column (Wagner). It sets the stop key when the first record occurs which no longer meets the condition (Walker).

Result of the EXPLAIN statement

TABLENAME

COLUMN_OR_INDEX

STRATEGY

PAGECOUNT

CUSTOMER

FULL_NAME_INDEX

RANGE CONDITION FOR INDEX

34

NAME

(USED INDEX COLUMN)

RESULT IS NOT COPIED, COSTVALUE IS

2

IN condition for an indexed column

Example Example

EXPLAIN SELECT *

  FROM hotel.customer

    WHERE name IN ('Smith','Miller')

  • Qualification: name = IN ('Smith','Miller')

  • Primary key of CUSTOMER table: cno

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

  • Search strategy: IN CONDITION FOR INDEX

End of the example.

The database system could also have used the corresponding RANGE condition for this SQL statement. This would have been much more expensive with the specified values however.

Result of the EXPLAIN statement

TABLENAME

COLUMN_OR_INDEX

STRATEGY

PAGECOUNT

CUSTOMER

FULL_NAME_INDEX

IN CONDITION FOR INDEX

34

NAME

(USED INDEX COLUMN)

RESULT IS NOT COPIED, COSTVALUE IS

21

Equality condition for an indexed column

Create a one-column index EMPLOYEE_ENO using the ENO column. See SQL Reference Manual, CREATE INDEX Statement (create_index_statement)

Example Example

EXPLAIN SELECT eno, hno

  FROM hotel.employee

    WHERE eno >= 8

  • Qualification: eno >= 8

  • Primary key of EMPLOYEE table: hno, eno

  • Indexes via qualified columns: EMPLOYEE_ENO (eno)

  • Search strategy: RANGE CONDITION FOR INDEX

End of the example.

The database system can execute this SQL statement with an INDEX ONLY strategy because only columns from the index and the primary key were entered in the SELECT list and the primary key columns are also stored in the index tree

To access the data records, the database system uses the EMPLOYEE_ENO index. It sets the start key when the first record occurs that meets the >= condition (value 8). It sets the stop key at the last record of the index.

Result of the EXPLAIN statement

TABLENAME

COLUMN_OR_INDEX

STRATEGY

PAGECOUNT

EMPLOYEE

EMPLOYEE_ENO

RANGE CONDITION FOR INDEX

45

ONLY INDEX ACCESSED

ENO

(USED INDEX COLUMN)

RESULT IS NOT COPIED, COSTVALUE IS

45

Note Note

For a BETWEEN condition, the stop key is not set at the end of the index, but at the last entry that fulfills the RANGE condition.

End of the note.

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