Search Conditions for Key Columns

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

Search conditions for key columns: Overview
Search Condition	Search Strategy of SQL Optimizer
Equality condition for every key column	The database 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 database system accesses the corresponding table row(s) directly.
Range Condition	The database system forms an upper and lower limit for the permitted key area for the key columns. All search strategies, including strategies for inversions, use information on this permitted key range.

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 a key column

Example Example

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

End of the example.

Since the primary key with the search condition has been fully qualified, the database system accesses the data record 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 for a key column

Example Example

EXPLAIN SELECT *

FROM hotel.city

WHERE zip IN ('04270','04570','04770')

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

End of the example.

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 for a key column

Example Example

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

End of the example.

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 can find additional simple example SQL statements in the SQL Tutorial.