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