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: How are joins performed when using a database link?

Re: How are joins performed when using a database link?

From: Rob Cowell <rjc4687_at_hotmail.com>
Date: Mon, 17 Mar 2003 14:44:19 +0000
Message-ID: <3E75DF43.F49480D1@hotmail.com>

Detlef B wrote:
>
> Daniel Morgan
> >
> > But whatever happened to benchmarking? Why are you asking us instead of
> > running a test?
> >
>
> it often happens here that guys spend more time typing out a request
> for advice than actually having a go, making a few mistakes & testing
> things out.

I think you are being a bit harsh on this poster.

To determine what Oracle is doing internally to retrieve rows takes a bit more than just 'benchmarking'. To me, benchmarking is about performance but not necessarily about discovering the actual concepts.

I haven't seen this described in any of the 'techie' books, but obviously I haven't read them all. My understanding from some previous glances at trace files is that relevant rows can be retrieved and bought across to the driving site so long as predicates are in the WHERE clause of the statement and they are literals or bind variables not references to other columns in the statement, otherwise all rows are retrieved.

So

SELECT a.col1, b.col2
FROM taba a,

     tabb_at_dblink b
WHERE a.col1 = b.col2
AND c.col3 = 'THIS ROW';

Would execute SELECT a.col1 FROM tabb WHERE col3 = 'THIS ROW'; at the remote site.

But as far as I remember

SELECT a.col1, b.col2
FROM taba a,

     tabb_at_dblink b
WHERE a.col1 = b.col2
AND c.col3 = (SELECT value FROM lookup_table);

Would execute SELECT * FROM tabb; because it doesn't evaluate the subselect first.

I think the 'OTHERS' column on the PLAN_TABLE will show what is going to be executed at the remote site if you wanted to play around with it and find out.

Anything I know about it is based on fairly limited observations, if anyone knows the ins and outs for sure I'd be interested from a general knowledge point of view, not having an operational need to 'benchmark' this at the moment. Received on Mon Mar 17 2003 - 08:44:19 CST

Original text of this message

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