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 !