Sequential Search

In principle, every search can be performed sequentially across the entire table, row by row. This means that the simplest search strategy of the SQL Optimizer is the sequential search. This search strategy is selected in the following cases:

?     If a search condition has not been specified.

?     If a search condition has not been specified for key columns or for indexed columns.

If the possible non-sequential search strategies of the SQL Optimizer would be more costly than the sequential search, the table is processed sequentially.

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

SELECT * FROM hotel.city

Qualification: none
Primary key of the table CITY: zip
Indexes of qualified columns: none
Search strategy used
: TABLE SCAN

No search condition (qualification) has been specified.

Result of the EXPLAIN Statement

TABLENAME

COLUMN_OR_INDEX

STRATEGY

PAGECOUNT

CITY

 

TABLE SCAN

11

 

 

RESULT IS NOT COPIED,
COSTVALUE IS


11

SELECT * FROM hotel.city
  WHERE name = 'Oxford'

Qualification: WHERE name = 'Oxford'
Primary key of the table CITY: zip
Indexes of qualified columns: none
Search strategy used
: TABLE SCAN

The search condition is not specified by means of the primary key column.

Result of the EXPLAIN Statement

TABLENAME

COLUMN_OR_INDEX

STRATEGY

PAGECOUNT

CITY

 

TABLE SCAN

11

 

 

RESULT IS NOT COPIED,
COSTVALUE IS


11

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

See also:

EXPLAIN Statement

Search Conditions for Key Columns

Search Conditions for Indexed Columns

List of All Search Strategies