RE: DB Links (VLDB)

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Gogala, Mladen 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 = 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_at_labor.state.ny.us]

> 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

>
>


> 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_at_inter.net.il]

> 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