|
Queries with an unknown search condition
Queries sometimes have an unknown search condition, such as in the case
when the statement's WHERE clause involves dynamic parameters that are
known only at execution time and not at compilation time, or when the statement
involves a join. For example:
-- 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.)
|
