Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: dblink and tnsname

From: TurkBear <jgreco1_at_mn.rr.com>
Date: Thu, 25 Jul 2002 10:36:48 -0500
Message-ID: <0m60kusgrj91l2msg45vj0cnetfr7gd3kv@4ax.com>

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" <richard.foote_at_bigpond.com> 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
>consider.
>
>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
>
>Richard
>
>"xueyang" <xueyangy_at_singnet.com.sg> wrote in message
>news:ahodm3$90e$1_at_reader02.singnet.com.sg...
>> 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
>connects
>> 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 ==----------

   http://www.newsfeed.com 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

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US