Search Strategies for Indexed Column Search Conditions

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

Search Strategies for Indexed Column Search Conditions
Search Condition	Search Strategy
Equality Condition for indexed columns	The database system accesses those rows for which keys are contained in the 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 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.

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

Example

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

Equality Condition for an Indexed Column

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

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`

Because the equality condition was 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 in the first data record that meets the equality condition in the first index column (Wagner), and the stop key in the first record that does not meet the condition (Walker) any more.

If you add a second equality condition, the system uses the EQUALITY CONDITION:

EXPLAIN SELECT *

FROM hotel.customer

WHERE name = 'Wagner' AND firstname = 'Walküre'

Qualification: name = 'Wagner', firstname = 'Walküre'
Primary key of CUSTOMER table: cno
Indexes via qualified columns: FULL_NAME_INDEX (name, firstname)
Search strategy: EQUALITY CONDITION

Result of the EXPLAIN Statement
TABLENAME	COLUMN_OR_INDEX	STRATEGY	PAGECOUNT
`CUSTOMER`	`FULL_NAME_INDEX`	`EQUAL CONDITION FOR INDEX`	`34`
	`NAME`	`(USED INDEX COLUMN)`
	`FIRSTNAME`	`(USED INDEX COLUMN)`
		`RESULT IS NOT COPIED, COSTVALUE IS`	`2`

IN Condition for an Indexed Column

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

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`

For this SQL statement, the database system could also have used the corresponding RANGE condition. However, this would have been much more expensive for the specified values.

Range 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).

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

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`

The database system can execute this SQL statement with an INDEX ONLY strategy because only columns from the index and the primary key are contained in the SELECT list and qualification part, 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 in the first record that meets the ≥ condition (value 8), and the stop key in the last record of the index.

More Information

Analyzing Search Strategies Using the EXPLAIN Statement

Search Strategies

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