Global optimization
The SQL compiler works in three phases, which help to produce an optimal access strategy for evaluating a query referencing a remote data source. These phases are pushdown analysis, global optimization, and remote SQL generation.
For a query submitted to the federated database, the access strategy might involve breaking down the original query into a set of query fragments and then combining the results of these query fragments.
- The cost to evaluate the operation
- The cost to transmit the data or messages between the federated server and the data sources
The goal of global optimization is to produce an access plan that optimizes the query operations on all data sources globally, across the federated system. An access plan that is globally optimal has the least overall cost of execution in a federated system. The remote SQL generation phase reverse translates the globally optimal plan into query fragments that are executed by individual data sources.
The SQL compiler has a knowledge base that contains characteristics of supported data sources and metadata about the data at those data sources. The optimizer does not generate SQL, query fragments, or plan hints that the remote data source cannot understand or accept.
Many factors can affect the output from global optimization and thus affect query performance. The key factors are server characteristics and nickname characteristics.
Relational and nonrelational wrappers differ in the details of how an access plan is produced, but the concept and final effect are the same.