performance over dblink

From: Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>
Date: Fri, 9 Dec 2011 09:14:22 +0100
Message-ID: <4EE1C35E.1090705_at_nordea.com>



hi,

we have a let's say theoretical dispute what is the best way to go with the following stuff:

we have 2 databases. On one of them there are packages which call some objects from the other one.
There are 4 possibilities:

  1. to call directly over dblink, for example: select fld1 into x1 from t1_at_db2 t1, t2_at_db2 t2 where t1.id=t2.id
  2. to cover db2 complexity with views, for example select fld1 into x1 from v_remote_at_db2
  3. to cover db1 link with views, for example select fld1 into x1 from v_local (where v_local is select fld1 from t1_at_db2 t1, t2_at_db2 t2 where t1.id=t2.id)
  4. to cover db2 complexity with views and cover db1 link with views, ie. select fld1 into x1 from v_local (where v_local is select fld1 from v_remote_at_db2 and v_remote is select fld1 from t1 t1, t2 t2 where t1.id=t2.id)

Covering db2 complexity is more user-friendly and from developer's point of view provides us kind of entry interface into db2 (we may then change some views into mviews or change the internal structure of the db2)

Covering db1 db link with views provides kind of "local" interface to remote database (making easier possible changes from remote views to local mviews on remote views, etc, and simplifying such operations as compilation of code on db1 side).

So from developer's point of view it seems the option 4 is a way to go. What about practical side of this construction? Which option would perform better? Is the overhead important if we call through one or two views (ie. opt2 or opt3 vs opt4)?
Of course as this is a theoretical construct I do not want to go into the special cases, as this is always a matter of details - I would like to have a rule of thumb from which I would start consideration of real cases.
What do You think about it?

My choice at the moment is option 4. Calling remote views would produce not worse execution plans than calling a join on remote tables. The performance changes (ie turning some views into mviews) are possible on both sides. The compilation of the db1 code does not touch objects on db2 (we had a case here with frequent changes of code, views structure changes were very rare, and compilation lasted very long due to call to remote objects to quite distant db).
What I miss here?
I 've read T.Kyte answers to various db link question and he suggested always using views, but still I have 2nd, 3rd and 4th option

TIA
Remigiusz

-- 
Pole nakazi

----------------------------------------------------------------------
Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>
pos   : DBA at DIiUSI
addr  : Nordea Bank Polska SA, Luzycka 6A Street, 81-537 Gdynia, Poland
phone : +48 58 667 17 43
mobile: +48 602 42 42 77
Nordea Bank Polska S.A. z siedzibą w Gdyni, ul. Kielecka 2, 81-303 Gdynia, 
wpisaną do Rejestru Przedsiębiorców Krajowego Rejestru Sądowego pod numerem: 0000021828, 
dla której dokumentację przechowuje Sąd Rejonowy Gdańsk - Północ w Gdańsku, 
VIII Wydział Gospodarczy Krajowego Rejestru Sądowego, 
o kapitale zakładowym i wpłaconym w wysokości: 277.493.500,00 złotych, 
NIP: 586-000-78-20, REGON: 190024711--
http://www.freelists.org/webpage/oracle-l
Received on Fri Dec 09 2011 - 02:14:22 CST

Original text of this message