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.
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.
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