Oracle Distributed query

[morg27]

I like to consider myself a seasoned oracle veteran, but have hit a wall on an issue. I am trying to run a distributed query against two very large tables, but the query is taking over an hour and I have to kill it everytime. When I run the query I want on each table seperately each completes fairly quickly. I think there may be an issue with the join. Also table b has its index on a different column than table a. The columns I am joining them on are types of number in table a and number(9) in table b, but have the same values. My query looks something like this.

select distinct a.location_id,
sum(a.number1), sum(b.number1),
sum(a.number2), sum(b.number2),
sum(a.number3), sum(b.number3),
sum(a.number4), sum(b.number4)
from tablea a, tableb@remotedb b
where a.location_id = b.store_number
group by a.location_id

 

[taupirho]

Lose the distinct for starters, completely irrelevant here.

How big are the tables?

Post an explain plan (after removing the distinct !)

What optimising method are you using COST or RULE based ?

Tables are definitely indexed on location_id and store_number - yes?

Any stats on the tables?





In order to understand recursion, you must first understand recursion.

 

[cmmrfrds]

Does every record in the table on remoteb have a value in location id? Can you add a Where Clause to your query to suppy Oracle with a predicate to pass on the remote query?