performance over dblink
- From: Remigiusz Sokolowski <remigiusz.sokolowski@xxxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 9 Dec 2011 09:14:22 +0100
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@db2 t1, t2@db2 t2 where t1.id=t2.id
2. to cover db2 complexity with views, for example
select fld1 into x1 from v_remote@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@db2 t1, t2@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@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@xxxxxxxxxx>
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--
//www.freelists.org/webpage/oracle-l
Other related posts:
