Re: Is it always true to join remote table first?

<emdproduction_at_hotmail.com> wrote in message news:1157132878.416440.42840_at_i3g2000cwc.googlegroups.com...
> Just had a discussion about the database link with my co-worker.
>
> If I have a table A, table B, both table will be on the remote site,
> and table c, d is on local.
> The remote is a non-oracle database.
>
> One theory is to create view v_AB on remote site for table A and B, and
> then join v_AB, c, d.
> The argument is if we do not join remote table first, Oracle will have
> to do a full table scan on all the remote site and bring in all the
> data in table A, B into DB buffer, causing a lot of traffic and other
> performance issue, is it statement true?
>
> Thanks for your help
>

I don't KNOW what will happen when the
remote database is a non-Oracle database, but the 'must create a view at the remote site' is rubbish for Oracle-only distributed joins.

If you can enforce a join order and join mechanisms that allow the remote tables to be joined remotely - and this often means making them the first two tables in the join order - then Oracle does not need to pull entire tables across the db link.

Bear in mind that one of the other options is for Oracle to use the remote indexes independently, which can mean lots of round trips across the network, and that can be even worse than pulling a projection of the table in one big hit. Consider, also, the need to maintain read-consistency for the duration of the query - the join mechanism may require you to set a non-standard isolation level to achieve this.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html