Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle automatic routing possible with distributed databases (v8.1.7) ?

Re: Oracle automatic routing possible with distributed databases (v8.1.7) ?

From: Tony <>
Date: Wed, 24 Jul 2002 15:32:05 +1000
Message-ID: <K%q%8.43$>

hi Nuno,

Thanks for your help. Another question i have:

  1. I did not know we can create 2 databases on 1 site. Are you saying 2 different SIDS can be created on 8.1.7 without using the OPS ?

"Nuno Souto" <> wrote in message
> "Tony" <> wrote in message
> >
> > What you are saying is that if i have more than 2 sites, say Brisbane,
> > Darwin etc ..
> > I would need to use the view and get the records. If the records are
> > of the time already available in the local database, will the latency
> > (slowness) still be there, ie, if Site A has the record and i am running
> > transaction at Site A, will the view still have the latency since all
> > different locations are in that one View.
> There is not necessarily a "latency" with Oracle. Either the select on
> the view succeeds or it doesn't.
> Think of it this way:
> If you have a predicate ("where" condition) that only fishes out
> the stuff from site A, then the SELECT on the view, when run from
> site A, will only read the rows in A. That will be fast. If the
> predicate does include rows from anywhere else, then they will
> be read from that somewhere else (slower).
> Thing you have to understand is that views do NOT pre-fetch all rows
> before considering the predicates of any SELECT against them. Instead,
> what happens is that the predicates are "added" to the base SQL that
> defines the view and THEN the lot is executed as if it was a whole new
> statement.
> However, note that if your join (or UNION) in the view puts no constraints
> whatsoever on say, siteB rows, then the situation I described above
> may in fact cause all rows from B to move to A. You have to be careful
> to add sufficient predicates to your SELECT to avoid Oracle shuffling
> stuff around unnecessarily. The way I usually work around this sort of
> problem is to use some form of column in the table that identifies from
> which site the row is. Then I can filter adequately if I don't want
> to have the lot.
> This BTW, will most likely have changed from version to version:
> remote query optimisation is evolving and has become a lot more
> intelligent of late. Do a trial on the particular version
> you're running. Use two local databases if you don't have a site A and
> site B. Do explain plan and check out what's being done.
> Make sure that a complete analyze has been done, including column
> histograms, in all sites!
> Best way of ensuring you get the results you want.
> > Please excuse my ignorance. Not well verse in Oracle.
> >
> Not a worry in the world. Sorry I took so long to reply,
> but my news server lost your msg. Just got it from google.
> Cheers
> Nuno Souto
Received on Wed Jul 24 2002 - 00:32:05 CDT

Original text of this message