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.

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.