![]() |
Internal Language Transformations
|
Reference Manual |
Transitive ClosureThe transitive property of numbers states that if A = B and B = C, then A = C. Cloudscape applies this property to query predicates to add additional predicates to the query in order to give the optimizer more information. This process is called transitive closure. There are two types of transitive closure: Transitive Closure on Join ClausesWhen a join statement selects from three or more tables, Cloudscape analyzes any equijoin predicates between simple column references within each query block and adds additional equijoin predicates where possible if they do not currently exist. For example, Cloudscape transforms the following query:
SELECT * FROM Groups, HotelBookings, FlightBookings
SELECT * FROM Groups, HotelBookings, FlightBookings On the other hand, the optimizer knows that one of these equijoin predicates is redundant and will throw out the one that is least useful for optimization. Transitive Closure on Search ClausesCloudscape 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), Cloudscape 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, Cloudscape then searches for a similar comparison (the same operator) between the column from the other table and the same constant. Cloudscape adds a new predicate if no such predicate is found. Cloudscape performs all other possible transformations on the predicates (described in Predicate Transformations) before applying transitive closure on search clauses. For example, given the following statement:
SELECT * FROM HotelBookings, FlightBookings Cloudscape first performs any other transformations:
Cloudscape then performs the transitive closure:
SELECT * FROM HotelBookings, FlightBookings When a sargable predicate uses the = operator, Cloudscape 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 HotelBookings, FlightBookings becomes (and is equivalent to)
SELECT * FROM HotelBookings, FlightBookings The elimination of redundant predicates gives the optimizer more accurate selectivity information and improves performance at execution time. |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |