For each
sargable
predicate where a
simple
column reference is compared with a constant (or the IS NULL and IS NOT
NULL operators), looks
for an
equijoin
predicate between the simple column reference and a simple column
reference from another table in the same query block. For each such equijoin
predicate, then
searches for a similar comparison (the same operator) between the column from
the other table and the same constant.
adds a new predicate if
no such predicate is found.
performs all other
possible transformations on the predicates (described in
) before applying transitive closure on
search clauses.
For example, given the following statement:
SELECT * FROM Flights, FlightAvailability
WHERE Flights.flight_id = FlightAvailability.flight_id
AND Flights.flight_id between 'AA1100' and 'AA1250'
AND Flights.flight_id <> 'AA1219'
AND FlightAvailability.flight_id <> 'AA1271'
first performs any
other transformations -- specifically, the BETWEEN transformation on the second
predicate:
AND Flights.flight_id >= 'AA1100'
AND Flights.flight_id <= 'AA1250'
then performs the
transitive closure:
SELECT * FROM Flights, FlightAvailability
WHERE Flights.flight_id = FlightAvailability.flight_id
AND Flights.flight_id >= 'AA1100'
AND Flights.flight_id <= 'AA1250'
AND Flights.flight_id <> 'AA1219'
AND Flights.flight_id <> 'AA1271'
AND FlightAvailability.flight_id >= 'AA1100'
AND FlightAvailability.flight_id <= 'AA1250'
AND FlightAvailability.flight_id <> 'AA1271'
AND FlightAvailability.flight_id <> 'AA1219'
When a sargable predicate uses the = operator,
can remove all equijoin
predicates comparing that column reference to another simple column reference
from the same query block as part of applying transitive closure, because the
equijoin predicate is now redundant, whether or not a new predicate was added.
For example,
SELECT * FROM Flights, Flightavailability
WHERE Flights.flight_id = Flightavailability.flight_id
AND Flightavailability.flight_id = 'AA1122'
becomes (and is equivalent to)
SELECT * FROM Flights, Flightavailability
WHERE Flights.flight_id = 'AA1122'
AND Flightavailability.flight_id = 'AA1122'
The elimination of redundant predicates gives the optimizer more accurate
selectivity information and improves performance at execution time.