Reflection of. UNION ALL based VDS

Hi,

I’m struggling with the following case.
I have a base VDS (call it VDS1) using joins between various PDS coming from an Oracle DB.
That VDS is based on a Year-Month calendar and is a bit less than 2 Millions records.
This VDS is also used to build 2 others (VDS2 and VDS3) that contains missing records in the calendar by duplicating the last known data to fills the gaps (so self join between VDS1 and VDS1)
Each of these two VDS are much smaller than the main one, less than 10000 records for VDS2 and about 2000 for VDS3.
These 3 VDS are used to create a 4th one that is the concatenation of them using “UNION ALL” so:
VDSMain = VDS1 UNION ALL VD2 UNION ALL VDS3

I created a RAW reflection on each VDS (1,2,3).
When I run the VDSMain request, the VDS 1,2,3 reflection are used (31s to complete), but then when I want to create the reflection for VDSMain, the refresh goes straight to VDS1 reflection with a duration of 10mn.

Moreover, even after the reflection for VDSMain has been built, all request to VDSMain goes also to the VDS1 reflection.
And here starts the real functional issue I have. VDSMain is used to calculed a rolling 12 months period on a 4th VDS (VDSRolling12).
Running the VDSRolling12 Request or creating/refreshing the reflection on that VDS goes also straight to VDS1 and runs for hours before completing.

Looking at the profile, reflection for VDS2 and 3 are not even considered.

Problem is that VDS2 and 3 are the longest to build even though they have the fewest line (self join generate an 11 Million records in request input for few thousands in output).

My guess is that during the planning phase, as all reflection refers to VDS1, the planner think he can find all records in it, which is not the case as VDS2 and 3 are here to create “new records”.
My guess is based on the fact that if I remove VDS1 from the UNION ALL of VDSMain, then VDS2 and 3 reflections are used.

Any insight or workaround to fix that?

Nb1: I’m not able to share the profile, but will in private message.
Nb2: I’m running a single node server on community edition 4.2.2

Thanks for your help,
Berni

Hi,
I think I’ve found the issue.
The biggest PDS that the VDS1 is relying on was missing one column in its reflection and that column was used in VDS2 and 3.
Since I added it to the PDS reflection things got lot better as the VDS2 and 3 can now rely on the VDS1 that have all the information.

Cheers,
Berni