Search Conditions for Key Columns 

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

Search Condition	Search Strategy of the SQL Optimizer
Equality condition for every key column	The system accesses the corresponding table row(s) directly.
IN Condition for a key column; and an equality condition for all other key columns (all columns that precede the column with the IN condition in the key)	The system accesses the corresponding table row(s) directly.
Range Conditions	Upper and lower limits for the valid key range are created for the key columns. All search strategies, including strategies using inversions, use the knowledge of this valid search range.

Examples

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

Concepts of the Database System, Objects in the Schema HOTEL

Equality Condition

SELECT name FROM hotel.customer
  WHERE cno = 7020

Qualification: cno = 7020
Primary key of the table CUSTOMER: cno
Indexes of qualified columns: none
Search strategy used: EQUAL CONDITION FOR KEY

Because the primary key was fully qualified by the search condition, the record is accessed directly.

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

IN Condition

SELECT * FROM hotel.city
  WHERE zip IN ('04270', '04570', '04770')

Qualification: zip IN ('04270', '04570', '04770')
Primary key of the table CUSTOMER: zip
Indexes of qualified columns: none
Search strategy used: RANGE CONDITION FOR KEY

Result of the EXPLAIN Statement

TABLENAME	COLUMN_OR_INDEX	STRATEGY	PAGECOUNT
CITY	FULL_NAME_INDEX	RANGE CONDITION FOR KEY	11
	ZIP	(USED KEY COLUMN)
		RESULT IS NOT COPIED, COSTVALUE IS	1

Range Condition

SELECT zip, name FROM hotel.city
  WHERE zip BETWEEN '10000' AND '20000'

Qualification: BETWEEN '10000' AND '20000'
Primary key of the table CUSTOMER: zip
Indexes of qualified columns: none
Search strategy used: 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

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

See also:

EXPLAIN Statement

Search Conditions for Indexed Columns

List of All Search Strategies