0

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

2
  • 1
    This is an issue known as the "multi-tenant" problem and (unfortunately! :-) ), there are no easy fixes or "correct" answers... Take a look at the threads here and links within for some discussion on this issue. I would tend towards keeping all of your data in the same instance - but maybe with different schemas? – Vérace Feb 7 '20 at 6:32
  • How many clients are we talking about? Do you have any regulatory requirements (GDPR etc)? – Colin 't Hart Feb 7 '20 at 8:40
0

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.

2
  • so if i have db1 and db2, you're saying I need to create a view lets say in db2 where it underlying query has a db link that retrieves both from db1 and db2? – lecarpetron dookmarion Feb 9 '20 at 22:49
  • Yes. May be a view (db1_db2) comprising all required columns of both databases put together - to start with. All your other queries can take from this view. Going further you shall create additional views based on conditions too. – Natarajan N Napoleon Feb 10 '20 at 3:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.