Re: Query Problems Across Database Link

On Oct 23, 2:26 pm, Brian Peasland <d..._at_nospam.peasland.net> wrote:
> 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
> d...@nospam.peasland.nethttp://www.peasland.net
>

Brian has a point. You need to look to see how Oracle is trying to solve the query. Run an explain plan. The OTHER column contains the SQL being sent to the remote db. Explain it on the remote db.

I have not read Brian's paper but where you usually try to convert subqueries to joins on local queries with distributed queries you often do just the opposite. That is you convert a join into a sub-query where the subquery is on the remote table via indexed columns. And sometimes as Brian mentioned you can push the query to the remote site and have Oracle run it there sending back the results.

HTH -- Mark D Powell -- Received on Mon Oct 23 2006 - 15:44:00 CDT