You can translate the question and the replies:

Is it possible to force a SQL Query distribution on each datasource, when there is an Union view ?

Hello, supposing the following picture: - I have a Union view (e.g. named U1) based of the Union of 2 views (e.g. T1_S1, T1_S2), each comming from many different oracle data sources (e.g. S1, S2 data sources) - these T1_S1, T1_S2 views are in fact pointing on a same table T1, but in different DBs (e.g. DB1, DB2, as used in S1, S2) - I have another view T2_S3 comming from a third oracle data source S3 (S3 using another oracle DB3) I need to execute, some external (e.g. from BO or other unpredictable join combination) SQL query using the Union view U1 (so T1_S1 Union T1_S2) and joined with T2_S3. e.g.: select * from U1, T2_S3 where U1.c1 = T2_S3.c2 Here Denodo is retrieving data from U1 in memory, then from T2_S3 in memory, then is executing the join in memory. This is not always optimized, even with forced nested loop. Anyway, the "nested loop" choice must depend of the query complexity, and the nesteed loop is not applicable in such a free query (not known in advance), as not defined as a join view in VDP. My Question: Is it possible to force denodo to execute such a query in the following way? select * from T1_S1, T2_S3 where T1_S1.c1 = T2_S3.c2 UNION select * from T1_S2, T2_S3 where T1_S2.c1 = T2_S3.c2 In distributed DBs architecture based on Oracle only, this last query is optimized by the Oracle core, thanks to the 2 separated sub-queries. Thanks for your valuable help.
11-04-2016 12:17:19 -0400

1 Answer

Hi, At this moment, they only possible way to force to execute the query in the way that you propose will imply to build the views in that specific order (first the join views and then the union of those two views). Anyway, there are plans to incorporate new optimizations in future updates and your suggestion will be considered in order to do this automatically. Hope this helps!
Denodo Team
13-04-2016 13:05:50 -0400
You must sign in to add an answer. If you do not have an account, you can register here