Current Limitations of the Cost-Based Optimization Process¶
The cost-based optimization process can be only applied to a query when there are statistics for all the base views involved in the query.
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
V2 are from the same database, Virtual DataPort
pushes down the join operation to the database.
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 only when 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
Although possible, we do not recommend enabling the cost-based optimization in views obtained from Multidimensional Databases or 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.