paulruizendaal wrote:

>> It should be handled at the RSE level instead.
>>
>
> Agree.
>
> I looked at this about 18 months ago and I thought that the most likely
> route to get this done would be to extend the external table code.
> Instead of performing file ops, the code would issue SQL commands to
> another FB instance or an ODBC driver.
>
> Simplistic code is not all that hard to do (but not so easy that I got
> it done back then). The difficulty is in optimization. Rather than
> retrieving a long record set and filtering that, clever code should
> push as much of the processing to the remote source to minimize the
> amount of data in transit. This means that the FB optimizer should take
> into account if part of a WHERE clause can be factored out, and
> included into the remote SQL request.
>
> Taking optimization further, it could try to figure out if makes sense
> to push sorting operations to the remote source as well. A very clever
> approach would take into account which indexes exist on the remote
> source and use that info in deciding an access plan.
>
>

We've traditionally called it mega-database manager. The right way to
implement it is as a separate provider under Y-valve / dispatch module l
looping back through the Y-valve / dispatch module to access target
databases. This allows it to access local, remote, and external
databases through gateways. The meta-database manager should be able to
handled both statically declared meta-databases, defined in terms of
existing database, and dynamically declared sets of source databases.

It would be a dreadful mistake to try to implement the mega-database
inside a database engine. The structures, dynamics, and optimization
strategies are all wrong. A composite engine would be functionally
limited, a bad database engine, and a bad mega-database manager.

The idea of the meta-database manager has been around since
pre-Interbase days. The only reason that it has never been implemented
is that no customer has ever been willing to actually pay money to have
it developed (Interbase) and not developer has ever been willing to take
it on (Firebird).

MySQL, incidentally, has a "Federated" storage engine that is more or
less a poor man's mega-database manager. The MySQL server architecture
makes some parts of Federated very easy but others virtually
impossible. Joins, for example, are performed in the upper levels of
the server and consequently can't be passed to the storage engine.

--

Jim Starkey, Senior Software Architect
MySQL AB, www.mysql.com
978 526-1376