Re: Query Problems Across Database Link

DDayDawg wrote:
> I am working with two Oracle 9i databases. One we are using as a data
> warehouse to hold selective information from the other (which of course
> is being pulled over the database link). The problem is my programmers
> are telling me that they cannot pull the data over as the normally
> would in a single database environment because the system is hanging.
>
> The primary table they are pulling from is large, roughly 750,000,000
> rows. That is on the foriegn side of the link. So if D1 is the Data
> Warehouse and D2 is the Production system the command they are using
> is:
>
> select a.*
> from table_a a, /*(in D1)*/
> table_b_at_d2 b
> where a.active_ind = 1
> and b.row_id = a.row_id
> and b.updt_dt_tm != a.updt_dt_tm
>
>
> What it looks like it's doing to me is it seems to be trying to pull
> the entire table_b across the database link into temp space so that it
> can do comparisons. I'm really hoping this isn't how the database link
> is supposed to work or we are going to have some real problems. Anyone
> have a clue of what we are doing wrong?
>

Is table A pretty small? If so, then instead of making the local database the driving site for this distributed query, you might want to consider making "d2" the driving site. On my web site (URL below), you will find a white paper titled "Tuning Distributed Queries and the DRIVING_SITE Hint" which may be useful to you.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown