Query that marries data between two databases

database-designmulti-tenantoracleoracle-12c

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

Best Answer

If you have doubts / questions about your database design, you can improve your question and continue.

Otherwise, let us assume that the database design is perfect - atleast as databases.

You shall start creating views - per requirement - comprising of both databases. And can make all your other select queries to look at the appropriate view.

They are much faster.

Arriving at a most wanted set of columns of rows matching the where condition through a view is easy.

You shall even think of arranging a view which has everything into it (all rows and all columns - just eliminating duplicates) and use that view in all your queries. This itself will never hinder the performance for sure.