applies transitive closure on search clauses after transitive
closure on join clauses. 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:
- 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.