You can translate the question and the replies:

How to stop Aggregate view from building sub query?

Does anyone recognize this pattern in Denodo? And preferably a way to prevent it? Base View (or any view) is selected by a Derived View which is aggregating data. And a filter is given to the Aggregate on one of the columns. Denodo is creating a large sub query to do the full "unfiltered" aggregate query underneath the filter statement. Which can and does result in some very poor performance to have to run the full aggregate every time. Example: Base View = col1, col2, col3 Derived View (selection of BV) = Group by (col1, col2), col4 = SUM(col3) Query Intention = select * from DV where col2 = 'x' Query Plan in Denodo: SELECT s0.col1 as col1, s0.col2 as col2, s0.col4 FROM ( SELECT t0.col1 as col1, t0.col2 as col2, sum( t0.col3) AS col4 FROM BV t0 GROUP BY t0.col1, t0.col2 ) s0 WHERE s0.day_ren = ? I'd like to make Denodo handle it this way (which is much more efficient): SELECT t0.col1 as col1, t0.col2 as col2, sum( t0.col3) AS col4 FROM BV t0 WHERE s0.day_ren = ? GROUP BY t0.col1, t0.col2 Any suggestions?
user
14-10-2015 18:57:26 -0400

1 Answer

Denodo applies that transformation when it does not push down the SQL to the sources. However it leaves the SQL "as is" when it's pushed down, as that is usually done by the database optimizer. If you want to enforce a particular SQL, you have to options: a) Use "Create Base View from Query" to define the view. To have a more generic view, you can take advantage of the ExecuteIfIsNotNull option, for example SELECT t0.col1 as col1, t0.col2 as col2, sum( t0.col3) AS col4 FROM BV t0 ^ExecuteIfIsNotNull("WHERE ",@WHEREEXPRESSION,"") GROUP BY t0.col1, t0.col2 You can find more information about ExecuteIfIsNotNull and WHEREEXPRESSION in the Adminisrtator Guide, section 4.2.1.6.1 Using the WHEREEXPRESSION Variable b) If you prefer not to use base view from query (for example to preserve the lineage), you can use "view parameters" in the "Model" tab of the definition of the derived view, and then a WHERE clause that uses that parameter. However, this will force you to specify a value for that input field, so it will be less generic than a)
Denodo Team
15-10-2015 15:30:43 -0400
You must sign in to add an answer. If you do not have an account, you can register here