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.
