Sequential Search (Table Scan)
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.
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
|
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
|
TABLENAME |
COLUMN_OR_INDEX |
STRATEGY |
PAGECOUNT |
|---|---|---|---|
|
CITY |
TABLE SCAN |
11 |
|
|
RESULT IS NOT COPIED, COSTVALUE IS |
11 |
Displaying the Search Strategies Used by SQL Statements (EXPLAIN)
You can find additional simple example SQL statements in the SQL Tutorial.