The access plan
The way that the optimizer chooses to read a table is called an access plan. The simplest method to access a table is to read it sequentially, which is called a table scan. The optimizer chooses a table scan when most of the table must be read or the table does not have an index that is useful for the query.
The optimizer can also choose to access the table by an index.
If the column in the index is the same as a column in a filter of
the query, the optimizer can use the index to retrieve only the rows
that the query requires. The optimizer can use a key-only index
scan if the columns requested are within one index on the table.
The database server retrieves the needed data from the index and does
not access the associated table.
Important: The optimizer does not choose a key-only scan for a
VARCHAR column. If you want to take advantage of key-only scans, use
the ALTER TABLE with the MODIFY clause to change the column to a CHAR
data type.
The optimizer compares the cost of each plan to determine the best one. The database server derives cost from estimates of the number of I/O operations required, calculations to produce the results, rows accessed, sorting, and so forth.