Re: Odd behavior with queries having DB link in 19C

Thank you so much Jonathan. You are spot on.

 It was an in -place upgrade. Btw, I think we are hitting the 20 index restriction here as one of the newest indexes having a very high numbered object_id and was created recently. So if i am correct , as the newly created index is having the highest object_id now, So the only workaround in this case would be to prioritize and drop and create the first ~20 top index by priority , those we will be mostly utilizing for the remote queries, so that they will have highest object_ids.

On Fri, Nov 12, 2021 at 5:52 PM Jonathan Lewis <jlewisoracle_at_gmail.com<mailto:jlewisoracle_at_gmail.com>> wrote:

The cost figures show that this database knows that the access path to the first remote table is awful, and you've said that the stats on the remote index haven't really changed even though the sample size has.

This suggests that this database doesn't know about the remote index, or thinks it can't be used. This prompts two thoughts: a) when you upgraded from 11g to 19c did you upgrade in place, or did you create a new database and export/import - if the latter then maybe you've changed the database character set and there's a character converstion required that makes this database think the other database won't be able to use the "obvious" index. b) for distributed queries Oracle will only consider the first 20 indexes on any individual table at the remote site, so if some dropped and recreated indexes during the upgrade (for whatever reason) then maybe the index that was being used is no longer in the first 20 for that table.