Hello. I am a novice in the data integration field. We have multiple Oracle databases, and we are looking at different options that will help to make things more organized and provide the same level of service to users.
I have a few questions:
- When to consolidate data sets into a single database?
- What are the advantages and disadvantages of integrating at the database, application and presentation layers? When is each appropriate?
I would really appreciate any help on the matter. Thanks.
There are a number of questions you need to ask:
- Are the databases related in any way?
- Do you carry out a lot of inter-database transactions?
- What are the databases used for (i.e., are they DSS or OLTP or a mix)?
If the answer to (1) and/or (2) is yes and the workload is similar(ish) then it may be better to consolidate databases. Even if the workload is different then you can look into the "resource manager" to segregate resources. At one site I had five separate but related databases for a very large data warehouse. I combined all five databases into one large one with data still separated out using different schemas. The resultant database, although large, was easy to manage, and the query + load time for the majority of the processes improved.
This was first published in May 2006