Re: How are joins performed when using a database link?

I have several tables that I use to load data. I have benchmarked and it seems to be different depending on the rows in each table, the columns requried to join, and other factors. These factors change over time so bringing over the entire table may be best to start with but a month later it's better to only bring what is needed.

I was only giving and example and not not really asking how I should do it. I'm asking how the join works. I want to know what Oracle is doing so I can make a better decisions.

From what I have seen I think if when I join using th elink Oracle brings over every row with only the columns needed for the join. It then performs the joins and retrieves all the columns for the rows returned by the join. This would mean when I have a small number of columns (with a small amount of data) to be joined it's better to only get what I need. If I only need the columns I'm using to join it's better to bring the entire table.

I have deduced the above from my benchmark tests. I don't know it's doing this. I would like to hear what others have to say. Maybe someone knows what it is doing.

Don

On Sun, 16 Mar 2003 20:43:26 -0800, DA Morgan <damorgan_at_exxesolutions.com> wrote:

>Don wrote:
>
>> We are doing lots of data loads using database links and I need some
>> advice.
>>
>> I have a part table and want to load all parts I do not have.
>> Should I bring the entire table over to a temp table and then get the
>> records that I want?
>> Or should I join across the database link and pull back just the
>> records I want?
>>
>> I'm not sure where the join is performed.
>> If the entire table must come over and then join in memory on my local
>> box it would probably be better to pull all the data first, then join
>> in another query. But if it can somehow restrict the rows and send me
>> just what I want it would be better to join accross the link and send
>> less data.
>>
>> Any advice?
>>
>> Don
>
>Without knowing what percentage of the records would be brought over and
>then ignored how can anyone help you?
>
>My instinct would be that you only grab what you need.
>
>But whatever happened to benchmarking? Why are you asking us instead of
>running a test?
>
>Daniel Morgan
Received on Mon Mar 17 2003 - 05:45:05 CST