RE: DB Links

  • From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
  • To: "'Gogala, Mladen'" <Mladen.Gogala@xxxxxxxx>, "Oracle-L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 23 Sep 2004 10:51:52 -0400

Mladen,

I only use views in the remote database.  The views already pre-join the
tables.  But I still experience the problem when the query joins the remote
view against local tables.  And I get the benefit by adding the hint to the
query.

Maybe changing them to in-line views is more appropriate (where the remote
view is in-line, thus making it the driving table).

That is until Jonathan reads this and starts saying the sql is not executed
linearly!!!  :)

I don't like leaving hints in production code either.  But this may be an
exception.

Tom Mercadante
Oracle Certified Professional


-----Original Message-----
From: Gogala, Mladen [mailto:Mladen.Gogala@xxxxxxxx] 
Sent: Thursday, September 23, 2004 10:47 AM
To: 'thomas.mercadante@xxxxxxxxxxxxxxxxx'; 'adar76@xxxxxxxxxxxx';
'adar76@xxxxxxxxxxxx'; 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@LINK
WHERE EMPNO = 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
> [mailto:thomas.mercadante@xxxxxxxxxxxxxxxxx] 
> Sent: Thursday, September 23, 2004 10:36 AM
> To: 'adar76@xxxxxxxxxxxx'; 'adar76@xxxxxxxxxxxx'; Oracle-L (E-mail)
> Subject: RE: DB Links
> 
> 
> Yechial,
> 
> The results of this hint are pretty amazing.  I have a query
> in production that runs in about 5-10 seconds.  When I apply 
> this hint, it runs in less than 1 second.  This is on 8.1.7.2.
> 
> Got to keep on testing it!  Thanks again!
> 
> Tom Mercadante
> Oracle Certified Professional
> 
> 
> -----Original Message-----
> From: Yechiel Adar [mailto:adar76@xxxxxxxxxxxx]
> Sent: Thursday, September 23, 2004 9:27 AM
> To: 'adar76@xxxxxxxxxxxx'; Oracle-L (E-mail)
> Subject: Re: DB Links
--
//www.freelists.org/webpage/oracle-l

Other related posts: