Monday, 1 October 2007

Database Links

There has been a long running debate at work about how to 'join databases together'. We have a number of different database each for their own application. However they share certain information e.g. user account data. Currently the databases are 'joined' by the PHP application software. I have long argued for using db links as a way to 'join' the data together, arguing that the CBO will do it in a far more efficient manner than any application ever could. A quick search on the asktom website came up with the answer. The best link turned out to be
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:14688146259705

My summary of it's conclusions goes as follows:-
1) should I use the application to 'join' different database together?
Answer:
definitely not , no way, nicht, never.

2) Should I use db links as part of my real time application code?
Answer: not unless you can absolutely avoid it, by all mean use DB links to copy data via materialized views or even in pl/sql that copies data from one db to another but anything that relies on both databases being up at the same time as part of a real time application should be avoided.

3) Should I use one of Oracle replication technologies to copy and sync data between different databases, so that real time applications are depend on one and only one database?
Answer: yes, were possible

4) Should I strive to keep everything on one database and use technologies like RAC to provide both processing power and resilience?
Answer: yes , yes yes.

No comments: