Search Conditions for Indexed Columns 

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

Search Condition	Search Strategy of the SQL Optimizer
Equality condition for the indexed columns	Those rows whose keys are included in the associated inversion list are accessed.
IN condition for indexed columns	The system accesses those rows whose keys are included in the inversion lists.
Range conditions with one condition only ( <, <=, >, >= ) for one of the two range limits (upper or lower limit)	The system accesses those rows whose keys are included in the inversion lists that were determined by the range.
Range conditions for which both range limits are specified. It is irrelevant to the selection of the search strategy whether this specification is made using a BETWEEN operator or using two conditions (<= or >= ) for the same column linked by AND.	The system accesses those rows whose keys are included in the inversion lists that were determined by the range.

In the case of the equality/IN conditions and the range conditions, there are also queries for which it is not necessary to access the rows, since all required values are already included in the inversion list(s).

Examples

The examples used to explain the SQL Optimizer are based on the demo database DEMODB and its demo data in the schema HOTEL.

Concepts of the Database System, Objects in the Schema HOTEL

Equality Condition

SELECT * FROM hotel.customer
  WHERE name = 'Wagner'

Qualification: name = 'Wagner'
Primary key of the table CUSTOMER: cno
Indexes of qualified columns: FULL_NAME_INDEX (name, firstname)
Search strategy used: RANGE CONDITION FOR INDEX

Because the equality condition is only entered in the first index column, this SQL statement must be processed with a RANGE CONDITION – there can be a number of entries with the value Wagner in the index.

The start key is set in the first record, which fills the equality condition in the first index column (Wagner). The stop key is in the first record that does not fulfill 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

SELECT * FROM hotel.customer
  WHERE name IN ('Smith', 'Miller')

Qualification: name = IN ('Smith', 'Miller')
Primary key of the table CUSTOMER: cno
Indexes of qualified columns: FULL_NAME_INDEX (name, firstname)
Search strategy used: IN CONDITION FOR INDEX

The SQL statement is processed with an IN condition. The SQL Optimizer also evaluated the corresponding RANGE condition, but this condition would be more costly for the values specified.

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

Range Condition

Create a one-column index EMPLOYEE_ENO using the ENO column.

See also:

SQL Reference Manual, CREATE INDEX Statement (create_index_statement)

SELECT eno, hno FROM hotel.employee
  WHERE eno >= 8

Qualification: eno = 8
Primary key of the table EMPLOYEE: hno, eno
Indexes of qualified columns: EMPLOYEE_ENO (eno)
Search strategy used: RANGE CONDITION FOR INDEX

This SQL statement can be processed with an INDEX ONLY strategy because only columns from the index and the primary key can be entered in the SELECT list, and the primary key columns are also stored in the index tree

Access is by means of the index EMPLOYEE_ENO. The start key is set in the first record that fills the bigger condition – that is, for the value 8.The stop key is set in 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

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

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

See also:

EXPLAIN Statement

Search Conditions for Key Columns

List of All Search Strategies