Re: join vs. IN on remote query

Using 'in' is usually bad. So I would use the second one. Or you can even try using an in line view. Turn on timing and tracing and see how it all looks. Here is how the inline view would look like:

select f1,f2 from case_header_at_f435 rch, (select case_id from case_tmp) ct
where rch.case_id = ct.case_id;

Berj

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
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue May 02 2000 - 00:00:00 CDT