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 Boyer