-- dynamic parameters SELECT * FROM Flights WHERE orig_airport = ? rollback -- joins SELECT * FROM Countries, Cities WHERE Countries.country_ISO_code = Cities.country_ISO_code -- complex search conditions SELECT * FROM Countries WHERE region = (select region from Countries where country = 'Spain')
In the above SELECT statements, the optimizer cannot get enough useful information from the index about how many rows will be returned by a particular access path. However, it can often make a good guess by looking at a table's selectivity for a particular WHERE clause.
Selectivity refers to the fraction of rows that will be returned from the table for the particular WHERE clause. The optimizer multiplies the number of rows in the table by the selectivity for a particular operation. For example, if the selectivity for a particular search operation is .10, and the table contains 100 rows, the optimizer estimates that the operation will return 10 rows. (This is not exact; it is just a good guess.)