How the SQL Optimizer Estimates the Costs of SQL Statements
The SQL optimizer estimates the costs of all the search strategies that the database system can use for a given SQL statement. For this, the SQL optimizer estimates how many I/O operations the database system would need to execute each search strategy. The estimation is based on random samples and statistical information supplied by the database system.
The cost of a search strategy largely depends on the form of the search condition specified in the SQL statement.
The SQL optimizer does not consider costs incurred by CPU time or memory usage.
You can use the EXPLAIN statement to display the search strategy and costs the SQL optimizer has selected for a given SQL statement.
Note that the SQL optimizer estimates costs using only the data sets that are currently available. For example, if you create an index for a table containing only a few data sets, the SQL optimizer might still decide to use the TABLE SCAN strategy, ignoring the index. For a similar table containing many data sets, the SQL optimizer might use another strategy instead, for example making use of the index.