Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: dblink and tnsname

Re: dblink and tnsname

From: TurkBear <>
Date: Thu, 25 Jul 2002 10:36:48 -0500
Message-ID: <>

To further confuse things:
A query on a remote database using the dblink will not be able to use the indexes on the remote database so queries are going to take longer, in most cases...

"Richard Foote" <> wrote:

>Hi again Xueyang,
>In answer to your specific questions:
>1) You would hope that the execution would take place on the remote site
>(DB2), and the resultant rows sent back to the local site (DB1).
>2) As this is not a distributed query, this can only be executed on the
>local site (DB2).
>However let me give you another example (this is not designed to confuse you
>but to clarify how all this works). This example will illustrate what I
>meant when I said it's up to the optimizer to decide how and from where to
>execute a distributed query.
>select * from bowie, ziggy_at_remotesite
>where bowie.hit_cds = ziggy.hit_cds and
> bowie.release_date > '01-JAN-1972' and
> ziggy.hair_colour = 'RED';
>The question now is which is the driving site ? Does Oracle retrieve rows
>from the remote ziggy table into the local site and perform the join locally
>or does Oracle send the rows from the local bowie table to the remote site,
>perform the join there and return the result set back to the local site ?
>The answer of course is that it depends. It depends on what the optimizer
>determines to be the cheaper option. So you see the answer to your question
>is not a simple yes/no, remote/local, as with most things, it depends. If
>you're dealing with one table, it's relatively simplistic but when you're
>dealing with several tables from several sites, it becomes a tuning issue to
>Probably the best place to read up on this stuff is the Distributed Database
>Manual, although the Administration and Concept manuals should also contain
>much information.
>Hope this makes some sense.
>Good Luck
>"xueyang" <> wrote in message
>> Thanks for the reply. Ok, let make my question clear.
>> conditions:
>> db1 on svr1, db2 on svr2.
>> Now i want to issue such a query on svr1 : select sum(fld1) from
>> table1(table1 resides on db2)
>> there are two ways to do this:
>> 1.
>> login to db1 first using sqlplus
>> then issue the query select sum(fld1) from table1_at_dblink1(dblink1
>> to db2)
>> 2,
>> login to db2 directly on svr1 by tnsname
>> sqlplus userid_at_tnsname/passwd
>> then issue the query select sum(fld1) from table1.
>> My question is: which server's meory the query is going to take for each
>> way? method 1, svr1 or svr2? method 2, svr1 or 2?
>> Btw, Do u know where can i find such topic in documentation?
>> Thanks in advance,

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==---------- The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =----- Received on Thu Jul 25 2002 - 10:36:48 CDT

Original text of this message