When a table scan Is better

Sometimes a table scan is the most efficient way to access data, even if a potentially useful index is available. For example, if the statement returns virtually all the data in the table, it is more efficient to go straight to the table instead of looking values up in an index, because then Derby is able to avoid the intermediate step of retrieving the rows from the index lookup values.

For example:

SELECT *
FROM Flights
WHERE dest_airport < 'Z'

In the Flights table, most of the airport codes begin with letters that are less than Z. Depending on the number of rows in the table, it is probably more efficient for Derby to go straight to the table to retrieve the appropriate rows. However, for the following query, Derby uses the index:

SELECT *
FROM Flights
WHERE dest_airport < 'B'

Only a few flights have airport codes that begin with a letter less than B.