RE: DB Links (VLDB)
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: "Oracle-L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 23 Sep 2004 11:16:33 -0400
Only a historical note, but this is one of a very few outstanding items from
the Oracle VLDB group of the early 1990's that has not been definitively
addressed. The hint was the interim solution and I don't think establishing
a statistical metric framework for inter-database access ever has reached
the action item level on anyone's priority list. I'm not sure it is even
reasonably do-able short of a fully deployed grid with knowledge of the
stats on all the databases involved in the query as well as the latency and
available capacity of the network paths required. Even then the best
statistical plan would be subject to actions external to the database engine
that sucked up (or stopped using) network capacity. And what abstract
costing should be put on network load? Should the cost be to minimize
response time or network load, or something else?
(I'll gratefully be educated on this point if there is a statistic the
optimizer could look at to sort out such plans.)
If Oracle wants to subsidize me writing a doctoral thesis on that I bet
there's a college near here that'd sign up for it, then wheeeeee! off I go!
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Gogala, Mladen
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
