
> 
> On Wednesday 16 April 2003 19:40, Rob Butler wrote:
> > 	a) create remoteview ... (all necessary information about remote machine
> > and query) b) create view [local | remote] .... (necessary info)
> > 	c) create view remote-server.schema.table (necessary info)
> 
> That is not a good way. Oracle does/recommends a create synonym so that either 
> a remote view or table can be treated as if it is a loacl table/view. This 
> takes care of select/insert/update/delete etc. I think that is a better way 
> of integrating remote objects in current database. 
> 
It may not be the "best" solution, but it is one that is possible to use now (if you don't want atomic remote updates) or could be done with atomic remote updates relatively soon.

It is similar to the way MS-SQL works.  And, once setup the local and remote tables all look the same to the client application.  I think that is important.

Later
Rob


Wed, 16 Apr 2003 11:31:04 -0400
Rob Butler <robert.butler5@verizon.net>
Re: cross-db queries (was Are we losing momentum?)
Darko Prenosil wrote:
> 
> Unfortunately any other way ends up with first selecting *ALL* records from 
> host ! If there is no such limitation I'll be pretty satisfied with dblink, 
> and will newer ask for "cross-db-queries" again !!!
> 

Yeah, this is why a proper implementation following the spec is needed. 
If the external access was part of the backend, then the planner could 
be taught to push down qualifiers to the external source where 
appropriate (I think -- maybe Tom will comment on this).

Joe


Wed, 16 Apr 2003 09:40:55 -0700
Joe Conway <mail@joeconway.com>
Re: cross-db queries (was Are we losing momentum?)
Joe Conway <mail@joeconway.com> writes:
> Darko Prenosil wrote:
>> Unfortunately any other way ends up with first selecting *ALL* records from 
>> host ! If there is no such limitation I'll be pretty satisfied with dblink, 
>> and will newer ask for "cross-db-queries" again !!!

> Yeah, this is why a proper implementation following the spec is needed. 
> If the external access was part of the backend, then the planner could 
> be taught to push down qualifiers to the external source where 
> appropriate (I think -- maybe Tom will comment on this).

Yes, the newer version of SQL-MED has APIs that allow this sort of thing
to be done.  Of course, it's another huge chunk of work beyond basic
SQL-MED ... but at least the roadmap is there, and when we get to the
end of the road we might even find other DBMSes that can speak the same
language.  If we invent our own spec the chance of handling cross-DBMS
queries intelligently is nil :-(

			regards, tom lane


Wed, 16 Apr 2003 13:35:45 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: cross-db queries (was Are we losing momentum?) 
On Wednesday 16 April 2003 15:31, Rob Butler wrote:
> > On Wednesday 16 April 2003 19:40, Rob Butler wrote:
> > > 	a) create remoteview ... (all necessary information about remote
> > > machine and query) b) create view [local | remote] .... (necessary
> > > info) c) create view remote-server.schema.table (necessary info)
> >
> > That is not a good way. Oracle does/recommends a create synonym so that
> > either a remote view or table can be treated as if it is a loacl
> > table/view. This takes care of select/insert/update/delete etc. I think
> > that is a better way of integrating remote objects in current database.
>
> It may not be the "best" solution, but it is one that is possible to use
> now (if you don't want atomic remote updates) or could be done with atomic
> remote updates relatively soon.
>
> It is similar to the way MS-SQL works.  And, once setup the local and
> remote tables all look the same to the client application.  I think that is
> important.
>

	...Except if you need only few records from remote database(on ISDN link for 
example) and remote view (dblink) first selects all the records from remote, 
and after that WHERE clause is executed on prepared result. 
I used dblink a lot, and the only way to avoid this was to create 
function(with parameters) that executes dblink , or to create view that  
sends the original query to the host (Which is why Joe added 
dblink_current_query() function to dblink at first place ). 
	First way has limitations because You can't add rewrite rule for the 
function(or at last I newer succeed with that).
	The second way has very bad limitation because you always must:
 SELECT * from remoteView(all the fields), otherwise you broke view 
definition, and you can't for example SELECT count(*) FROM remoteView.

Unfortunately any other way ends up with first selecting *ALL* records from 
host ! If there is no such limitation I'll be pretty satisfied with dblink, 
and will newer ask for "cross-db-queries" again !!!

P.S.: Sorry for bad English !


Wed, 16 Apr 2003 18:29:23 +0000
Darko Prenosil <darko.prenosil@finteh.hr>
Re: cross-db queries (was Are we losing momentum?)