Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: DB Links

RE: DB Links

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Thu, 23 Sep 2004 10:53:01 -0400
Message-ID: <4001DEAF7DF9BD498B58B45051FBEA650146C750@25exch1.vicorpower.vicr.com>


Mladen,

        My experience is somewhat different from yours. In the case of "FROM = EMP_at_LINK WHERE EMPNO =3D 1234" I have found that the local database will = pass that along to the remote database for resolution since there will = definelty be one and only one row returned. Now "FROM EMP_at_LINK WHERE = EMPNO > 1234" would also resolve itself on the remote db, but "FROM dept = a, EMP_at_LINK b WHERE a.empno =3D b.empno and b.EMPNO > 1234" will result = in emp becoming a local temp table.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
From: Gogala, Mladen [mailto:Mladen.Gogala_at_aetn.com] Sent: Thursday, September 23, 2004 10:47 AM To: 'thomas.mercadante_at_labor.state.ny.us'; 'adar76_at_inter.net.il'; 'adar76_at_inter.net.il'; Oracle-L (E-mail) Subject: RE: DB Links

I strongly dislike hints and I use them only when necessary. The problem with joins over the database link is that in situations like FROM EMP_at_LINK WHERE EMPNO =3D 1234, the whole EMP table will be brought over to the local site, stored in the temporary tablespace, much to the delight of users who want to do some sorting, and filtered locally, even in case that EMPNO is the primary key. How is it possible to filter remotely? Very simple, create a remote view, and access the view instead of the tables. I find that method to be much = more
portable and version independent then hints. Did I mention that I strongly dislike hints?

--
Mladen Gogala
A & E TV Network
Ext. 1216



> -----Original Message-----
> From: Mercadante, Thomas F=20
> [mailto:thomas.mercadante_at_labor.state.ny.us]=20
> Sent: Thursday, September 23, 2004 10:36 AM
> To: 'adar76_at_inter.net.il'; 'adar76_at_inter.net.il'; Oracle-L (E-mail)
> Subject: RE: DB Links
>=20 >=20
> Yechial,
>=20
> The results of this hint are pretty amazing. I have a query=20
> in production that runs in about 5-10 seconds. When I apply=20
> this hint, it runs in less than 1 second. This is on 8.1.7.2.
>=20
> Got to keep on testing it! Thanks again!
>=20
> Tom Mercadante
> Oracle Certified Professional
>=20 >=20
> -----Original Message-----
> From: Yechiel Adar [mailto:adar76_at_inter.net.il]=20
> Sent: Thursday, September 23, 2004 9:27 AM
> To: 'adar76_at_inter.net.il'; Oracle-L (E-mail)
> Subject: Re: DB Links
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l
Received on Thu Sep 23 2004 - 09:49:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US