You can translate the question and the replies:

Filtering Left Outer Joins with Automatic Simplification of Queries enabled causing execution plan to be complex

My development team is seeing strange behaviour with Denodo execution plans when using left outer joins and filters. We have a query that has multiple left joins to get data from a lookup table. When we execute the query, it takes approximately 20 seconds to return all data. This is the expected exection time. When we add a filter to one of the tables on the right of the left outer join, the query takes over 8 minutes to complete. Reviewing the execution trace of both queries, we find that the query with the filter adds a Projection Plan at every left outer join, which includes Projected Fields for every column of every view on the right side of the join, even the columns not used in the query. It also adds an additional inner join as the final join. These projection plans with every column and inner joins are causing the query to run extremely slow. Over 8 minutes to complete. Researching the knowledge base and support articles, I found a reference to left joins being converted to inner joins via the query optimizer due to an enabled setting called Automatic Simplification of Queries. If I add a CONTEXT clause to disable Automatic Simplification of Queries to my filtered query, CONTEXT ('simplify'='off'), the inner join and additional Projection Plans with Projected Fields are not added to the execution plan. The execution plan looks similar to the unfiltered query and the data is returned in 20 seconds. Is there a way to overcome this issue without disabling Automatic Simplification of Queries on queries impacted by this feature? Is this a bug in Denodo 7's execution engine or is this by design?
09-09-2020 18:34:13 -0400

1 Answer

Hi, It looks like in your case, you have only Static optimization enabled. Could you also try enabling Cost Based Optimization and gathering the statistics of the views? With this Denodo could generate a better optimization plan for this query. I would also recommend opening a case with Denodo Support Team if you are a vaid support user providing details of the query type you are executing along with the execution trace and the denodo update to get more information on if its an issue in the software. Hope this helps!
Denodo Team
10-09-2020 08:38:26 -0400
You must sign in to add an answer. If you do not have an account, you can register here