Search Strategies

The SQL Optimizer of the database system can generally use various different strategies (search strategies) to access data.

Among other things, these search strategies differ in the costs that they incur when executed.

Features

The simplest search strategy is the sequential search, in which the entire table is scanned row by row. However, for large quantities of data, the sequential search is often the most costly search strategy. For this reason, the SQL Optimizer analyzes other search strategies that scan smaller areas of the table.

The available search strategies depend on the search condition, the structure of the SQL statement and the design of the database (primary key and so on).

This documentation describes the following search conditions and the search strategies that result from them:

?     Search Conditions for Key Columns

?     Search Conditions for Indexed Columns

?     Search Conditions for UPDATE Statements

?     Search Conditions Linked with OR

?     Postponement of the Search to the FETCH Time

?     Joins

Activities

To find out which search strategy the SQL Optimizer has chosen for an SQL statement, use the EXPLAIN statement.

See also:

List of All Search Strategies

How the SQL Optimizer Operates

Concepts of the Database System, Database Design Tips