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 V1 and V2 are base views from the same database and V3 is a base view from another data source. Let us also suppose DV is a derived view obtained by joining V1 and V2. Because V1 and 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 V1, V2 and V3.

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 WHERE clause.

• This optimization is not applied to queries using the QUERYPLAN context option.

• 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.