Automatic Simplification of Queries¶
The Execution Engine tries to simplify the queries before the actual query execution takes place. The goal is to optimize the queries by applying optimization techniques that are always beneficial. For example, removing redundant operations, transforming outer joins to inner joins when possible, etc. As these optimizations are always applied, they are called static optimizations.
The query optimization process consists of two different phases: The static optimization phase and the cost-based or dynamic optimization phase.
In the static phase the optimizer analyzes the query and performs query rewritings that improve the performance regardless of the specific data we need to manage. These transformations include removing unnecessary JOINs or UNION partitions (branch pruning) or pushing conditions and group bys under Joins and Unions. Then, the cost-based optimizer analyzes the execution plan returned by the static optimization phase and estimates the costs of different possible plans. The alternatives considered include, among other variables: the different join strategies to use for each join (MERGE, HASH, NESTED) or moving partial results from some of the operations to another data source (Data Movement) or to a Massive Parallel Processing system.
This section describes the most important static optimizations.
The Automatic simplification of queries is enabled by default, but it can be disabled in the “Queries optimization” dialog of the menu “Administration > Server configuration” (see section Queries Optimization).
To enable/disable this optimization for a specific database, click Database management in the Administration menu, select the database and click Edit. Then, select Automatic simplification of queries on or off and click Ok. In this dialog, if “Automatic simplification of queries” is Default, the database has the configuration defined in the “Queries optimization” dialog.
These are some of the simplifications performed by Execution Engine:
Remove the branches of the query that are redundant: see section Removing Redundant Branches of Queries (Partitioned Unions).
Reorder the joins of the query to increase the number of operations that are delegated to the sources. This optimization is not performed when the result is a cartesian product because usually, cartesian products are very inefficient.
When possible, transform outer join into inner joins. In certain scenarios, some outer joins can be simplified to inner joins, without changing the result.
Push down GROUP BY operations below join views: see section Pushing Down GROUP BY Views Below JOIN Views.
Push down GROUP BY operations below union views: see section Pushing Down GROUP BY Views Below UNION Views.
For further reading, we recommend visiting the series of articles titled Best Practices to Maximize Performance, in the Denodo Knowledge Base.