Need some help understanding why reflection was not chosen

Table A and Table B are both have raw reflections created. Table B only changes once a day. Table A is “live” and could contain new info at any point throughout the day. I created a full outer join between A and B on 8 fields but when i run the query and check the profile I see this: * Raw Reflection (raw): considered, matched, not chosen."

How does one dig deeper into why the reflection was not chosen? Ideally I would like to refresh the raw reflection for table B once in the morning (its not going to change for the rest of the day) . However it seems the join between A and B skips the reflection on both A and B, and seems to pull from the original source.

Hi @nrchade

Can we please have the profile?

How To Share A Query Profile

Thanks
@balaji.ramaswamy

What file format is the original data in? Are you sorting or partitioning the data in a specific way for the raw reflection?

The optimizer will decide whether or not to use the reflection based on cost, so file formats and any sorting/partitioning will play a role.

Tables A and B are both tables in an Oracle database. I have a sort on one field on each reflection for an id number that is included in the join statement.

profile.zip (32.1 KB)

Hi @nrchade

This seems to be a known issue but I would be able to confirm if you can kindly send me the verbose profile. Click on Admin-Support and add the support key “planner.verbose_profile”, click on show and enable the parameter. Rerun the query and send us the profile

Thanks
@balaji.ramaswamy

Having the same king of issue here.
I’m joining 2 VDS with raw reflections, and such raw reflections are not chosen:
one of them with “Too expensive” reason, and the other one with “Did not cover query” reason.

Not sure how a raw reflection on all the fields of a VDS can be considered as “Did not cover query”.

Note: same queries were working fine in 3.0.1 before the migration to 3.0.5.
Could it be a regression ?

Working much better today after migration to 3.0.6