Re: Query Problems Across Database Link

Brian Peasland 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.
>

Excellent paper, I almost hate to post
https://metalink.oracle.com/metalink/plsql/f?p=130:10:7968161258662401359::::alltext,bug,numHits:driving_site,TRUE,100

But we all need to keep our eyes open when the code is like walking around in a Halloween funhouse.

jg

--
@home.com is bogus.
When precision kills:
http://blogs.law.harvard.edu/philg/2006/10/06/mid-air-collision-in-brazil-when-precision-kills/