Re: database link vrs. remote connect - a question

I agree scenario 1 should be faster if everything not mentioned is assumed to be the same on X and Y. He can determine whether remote table data should be fetched across the db link by using the hint DRIVING_SITE. Another reason remote queries are slower is that they generate transactions, which have their overhead not existing in plain local queries, such as using rollback segments and acquiring DX locks.

Yong Huang
yong321_at_yahoo.com

"Michael George III" <michael_george_III_at_hotmail.com_NOSPAM> wrote in message news:<gUG27.186066$DG1.31097592_at_news1.rdc1.mi.home.com>...
> The answer as always is "IT DEPENDS", but in general scenario 1 will be
> much, much faster.
>
> The reason for this is that when doing queries using links, the query is
> performed on the database which has the link. That means if you are on
> machine X and there is a link in database A to database B. If you login to
> database A and do a query using the link to database B, all the data blocks
> for the objects in the query have to be shoveled across the network from
> database A to database B and then the query is run. For large tables, this
> can take awhile. However, both boxes being equal, it would be faster to
> perfom the query directly on database B since all the data is local and most
> likely a lot of it would be cached in the SGA.
Received on Tue Jul 10 2001 - 23:19:42 CDT