Database 1 is the master database where data on all domains in our client's industry are stored
Database 2 (or Database N) are the application specific databases of our client. (e.g. Application ABC would have Database ABC, Application DEF would have Database DEF etc.). Common data between applications are stored in Database 1, the master database.
The issue is, for example we have Application ABC. Lets say doing transactions via that application updates data records in tables of Database 1. Doing that also inserts records on Database 2. A record in Database 1 would now have counterpart records of it in Database 2. Now when we do a read of those records we would need a query in both Database 1 and Database 2 then marry both result set programmatically in our application's back end. As queries become more complex and records grow in size, performance issues start to happen when doing reads. Does anyone know the most efficient way to solve this kind of problem? or this this say that we need to re-architect our data?
Having a DB Link is not an option now this that could further degrade performance. Our database by the way is Oracle 12c