Current Limitations of the Cost-Based Optimization Process¶
The cost-based optimization process can be only applied to a query when at least one of the following two conditions is met:
There are statistics for all the base views involved in the query.
Or, there are statistics for the leaf views of the query, even if they are not base views. Leaf views are those views that are entirely pushed down to a data source.
Example: Let us suppose
V2 are base views from the
same database and
V3 is a base view from another data source. Let us
DV is a derived view obtained by joining
DV is a leaf view because
V2 are from the
same database and Virtual DataPort pushes down join operations to
Let us suppose we execute the following query:
SELECT * FROM DV JOIN V3 ON DV.a = V3.a
This query can benefit from the cost-based optimization when any of the following conditions hold:
We have gathered the statistics of
Or, we have gathered the statistics of
When the cost-based optimization cannot be applied because the statistics of a required view are lacking or are disabled, the execution trace contains a message like “Missing statistics or not activated in the following nodes: DV, V3”
In addition, you have to take into account the following limitations of the cost-based optimization process:
This optimization is not applied in queries involving subqueries in the
This optimization is not applied to queries using the
Although possible, we do not recommend enabling the cost-based optimization in views obtained from Multidimensional Databases, Google Search data sources and ITPilot data sources. The cost-based optimization process makes certain assumptions that are not verified in those types of data sources. Therefore, cost estimations may produce unexpected results.
The n-joins involving any left, right or full outer join will not be reordered. Virtual DataPort will still try to find the best execution plan for each 2-join in the n-join, but it will execute them in the order specified by the query.