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

 

When searching the data records, the database system can always perform a sequential search (table scan) - that is, search through the whole table row for row. The sequential search (table scan) is the simplest search strategy of the SQL Optimizer.

The database system uses the sequential search (table scan) in the following cases:

  • If no search condition has 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.

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.

Example Example

EXPLAIN SELECT *

  FROM hotel.city

  • Qualification: None

  • Primary key of CITY table: zip

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

End of the example.

No search condition (qualification) has been specified.

Result for the EXPLAIN statement

TABLENAME

COLUMN_OR_INDEX

STRATEGY

PAGECOUNT

CITY

TABLE SCAN

11

RESULT IS NOT COPIED, COSTVALUE IS

11

Example Example

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

The search condition does not refer to the primary key column.

Result for the EXPLAIN statement

TABLENAME

COLUMN_OR_INDEX

STRATEGY

PAGECOUNT

CITY

TABLE SCAN

11

RESULT IS NOT COPIED, COSTVALUE IS

11
End of the example.

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

More Information