Re: join vs. IN on remote query

you can run an explain plan on both queries, though it may not be of great help because of the link.

Usual rule when joining remote tables is to force Oracle to do the join on the database that has the bigger table. Sometime oracle will move the bigger table over which is not desirable.

The in statement may help but it all depends on it using the index properly, if it doesn't it can cause problems

the explain paln on my system for the first is

1       SELECT STATEMENT         Cost = 77841   
2         HASH JOIN         Cost = 1   
3           REMOTE         Cost = 2  SERIAL_FROM_REMOTE
3           VIEW         Cost = 1   
4             SORT      UNIQUE   Cost = 1   
5               TABLE ACCESS   TD_BILL_RATE_RIDER   FULL   Cost = 1

second

1       SELECT STATEMENT         Cost = 111866   
2         MERGE JOIN         Cost = 1   
3           SORT      JOIN   Cost = 1   
4             TABLE ACCESS   TD_BILL_RATE_RIDER   FULL   Cost = 1   
3           SORT      JOIN   Cost = 2   
4             REMOTE         Cost = 1  SERIAL_FROM_REMOTE

yet when run in same schema without remote the 2nd one runs 5x faster with a better explain plan.

you pretty much need to try differnet hints and join orders and examine the indexes and so forth before you can dicide what is best

In article <8emq9s$m07$1_at_ins20.netins.net>, rodney_at_worf.netins.net (Rodney) wrote:
>
>I have a need to do one of the following, and I am
>trying to get information on which one would be
>"best" to use:
>
>select f1,f2 from case_header_at_r435
> where case_id in (select case_id from case_tmp);
>
>-OR-
>
>select f1,f2 from case_header_at_f435 rch, case_tmp ct
> where rch.case_id = ct.case_id;
>
>
>Here's more info:
>The remote database is 7.3
>The local databse is 8i
>The remote table (case_header) will have a lot more records
> than the local table (case_tmp). 10's of thousands vs. hundreds
> typically.
>The case_id field can be (is) indexed on both tables.
>
>
>My questions:
>Which method is faster?
>Where is temp storage used in each method?
>Which database is shouldering how much of the load in each method?
>Which method is "better"?
>
>Any other questions, hints, tips, thoughts?
>
>Thanks,
>rodney
  Received on Tue May 02 2000 - 00:00:00 CDT