Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How are joins performed when using a database link?
On Mon, 17 Mar 2003, rjc4687_at_hotmail.com wrote:
>
> 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.
Run this explain script from where the sql is executing.
set linesize 1000 set pagesize 100 set long 10000 column QUERY_PLAN format A110 truncate column EXECUTED_QUERY format A10000 truncate column PART format A4 column PARR format A4 SELECT LEVEL, RTRIM (LPAD (' ', 2 * level) || RTRIM (operation) || ' ' || RTRIM (options) || ' ' || object_name) QUERY_PLAN ,cost ,decode(object_node,NULL,'N','YES') PARR ,decode(partition_id,NULL,'N','YES') PART FROM plan_table CONNECT BY PRIOR ID = PARENT_ID START WITH ID = 0 ; select other EXECUTED_QUERY from plan_table where operation = 'REMOTE' ;
Now, take the "EXECUTED_QUERY" column to the remote server and run an explain plan on that.
That will tell you the two plans on both servers. You should be able to gleam the info you need from that.
Basically, if you are joining to more than one table on the remote server, create a view. (If you don't Oracle will most likely bring all tables data back)
You might need to actually create a view on the remote server which joins to the driving site bringing across a small subset of ids. This could whittle down the set coming back across for final joins on the driving site.
It is a series of trials, analysis and trials again, along with many trips to the documentation.
-- Galen BoyerReceived on Mon Mar 17 2003 - 09:01:14 CST