Re: dblink and tnsname

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,
>
>
>
>
Received on Thu Jul 25 2002 - 07:20:54 CDT