Background documentationSequential Search (Table Scan) Locate this document in the navigation structure

 

The simplest search strategy is the sequential search (table scan), in which the database system searches through the entire table, row-by-row. However, for large quantities of data, the sequential search is often the most complex and expensive search strategy.

The database system uses the sequential search in the following cases:

  • No search conditions have been specified.

  • Neither the key columns nor the indexed columns are included in the search condition.

  • The available non-sequential search strategies would be more costly than the sequential search.

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).

No Search Condition Specified

EXPLAIN SELECT *

  FROM hotel.city

  • Qualification: None

  • Primary key of CITY table: zip

  • Indexes via qualified columns: No search strategy used: TABLE SCAN

Result for the EXPLAIN statement

TABLENAME

COLUMN_OR_INDEX

STRATEGY

PAGECOUNT

CITY

TABLE SCAN

11

RESULT IS NOT COPIED, COSTVALUE IS

11

Primary Key Column Not Contained in Search Condition

EXPLAIN SELECT *

  FROM hotel.city

    WHERE name = 'Oxford'

  • Qualification: WHERE name = 'Oxford'

  • Primary key of CITY table: zip

  • Indexes via qualified columns: None

  • Search strategy: TABLE SCAN

Result for the EXPLAIN statement

TABLENAME

COLUMN_OR_INDEX

STRATEGY

PAGECOUNT

CITY

TABLE SCAN

11

RESULT IS NOT COPIED, COSTVALUE IS

11

More Information

Analyzing Search Strategies Using the EXPLAIN Statement

Search Strategies

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