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

"Tony" <giaptt_at_yahoo.com.au> wrote in message news:<f%aZ8.1$ZP3.8361_at_vicpull1.telstra.net>...
>
> 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 most
> 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 the
> transaction at Site A, will the view still have the latency since all the
> 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
nsouto_at_optushome.com.au Received on Mon Jul 22 2002 - 06:21:42 CDT