Join across servers

[steve]

Can you join two tables across different servers in mySQL, or am I
dreaming of Oracle?

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/mySQL-Join-s...ict225285.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=777346

[Thomas Bartkus]

Directly? I don't know.

BUT

If Microsoft Access is available to you, you can create an Access database
with tables linked to different MySQL servers and then join them as neat as
you please. You can then INSERT the results back to a MySQL server if you
wished.

You could even join a table residing on a MySQL server with another on an
Oracle server. OR a Postgres server, OR a SQL server, OR an HTML table, OR
an ancient DBase file. Access, with abundant ODBC drivers available for
anything, has this most amazing secret life as a data router.

Wish I knew how to pull off tricks like that using Linux software!
Thomas Bartkus

"steve" <Us************@dbForumz.com> wrote in message
news:4_***************************************@dbf orumz.com...

Can you join two tables across different servers in mySQL, or am I
dreaming of Oracle?

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/mySQL-Join-s...ict225285.html
Visit Topic URL to contact author (reg. req'd). Report abuse:

http://www.dbforumz.com/eform.php?p=777346

[Thomas Bartkus]

Directly? I don't know.

BUT

If Microsoft Access is available to you, you can create an Access database
with tables linked to different MySQL servers and then join them as neat as
you please. You can then INSERT the results back to a MySQL server if you
wished.

You could even join a table residing on a MySQL server with another on an
Oracle server. OR a Postgres server, OR a SQL server, OR an HTML table, OR
an ancient DBase file. Access, with abundant ODBC drivers available for
anything, has this most amazing secret life as a data router.

Wish I knew how to pull off tricks like that using Linux software!
Thomas Bartkus

[Bill Karwin]

steve wrote:

Can you join two tables across different servers in mySQL, or am I
dreaming of Oracle?

Does Oracle do this? That's news to me. I mean, I'm not denying that
Oracle might have this feature, I just don't know that it does.

Most RDBMS products parse and execute SQL on the server side. That is,
the client sends a SQL query as a string over the network. The server
reads the query, collects the data requested, and sends them back.

For a SQL query to execute against multiple databases on multiple
servers, the SQL would have to be parsed in the client software, which
introduces a whole host of other issues: does the SQL parser support
the same SQL dialects and extensions, or can the client SQL parser
translate the query features into syntax understood by each respective
server? What if your query references stored procedures or user defined
functions that are installed on the server and not on the client?

The only way to resolve a client-side join would be to copy _all_ the
values (or at least the indexes) from both servers to the client
machine, and sift through them, performing the join. What if you are
performing a join between two servers, which each store billions of
rows, even if you expect the result of the join to be a small set of a
few dozen rows? That could overwhelm your network, and your client
computer.

It's a very complex and ambitious prospect to do client-side joins
between multiple servers. Few database products attempt it, and those
that do, haven't solved it for all cases.

Regards,
Bill K.

[Tony]

Thomas Bartkus wrote:

Directly? I don't know.

BUT

If Microsoft Access is available to you, you can create an Access database
with tables linked to different MySQL servers and then join them as neat
as
you please. You can then INSERT the results back to a MySQL server if you
wished.

You could even join a table residing on a MySQL server with another on an
Oracle server. OR a Postgres server, OR a SQL server, OR an HTML table, OR
an ancient DBase file. Access, with abundant ODBC drivers available for
anything, has this most amazing secret life as a data router.

Wish I knew how to pull off tricks like that using Linux software!
Thomas Bartkus

"steve" <Us************@dbForumz.com> wrote in message
news:4_***************************************@dbf orumz.com...

Can you join two tables across different servers in mySQL, or am I
dreaming of Oracle?

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/mySQL-Join-s...ict225285.html
Visit Topic URL to contact author (reg. req'd). Report abuse:

http://www.dbforumz.com/eform.php?p=777346

I use SQL Server to perform the same types of tricks - DTS is a fairly
powerful package if used right.

Tony Mays

[Malcolm Dew-Jones]

Bill Karwin (bi**@karwin.com) wrote:
: steve wrote:
: > Can you join two tables across different servers in mySQL, or am I
: > dreaming of Oracle?

: Does Oracle do this? That's news to me. I mean, I'm not denying that
: Oracle might have this feature, I just don't know that it does.

Absolutely Oracle does this.

A query can pull in tables from any (Oracle) database with the necessary
connections, and potentially other database via a supported gateway.

This is not new. Oracle has done this for years.

There might be limitations of some sort, but not for any run of the mill
queries.

--

This space not for rent.

[Bill Karwin]

Malcolm Dew-Jones wrote:

Absolutely Oracle does this.

A query can pull in tables from any (Oracle) database with the necessary
connections, and potentially other database via a supported gateway.

This is not new. Oracle has done this for years.

Fascinating, thanks for the info. I admit I have nearly zero experience
with any version of Oracle. I was assuming that since it is potentially
so costly to perform a cross-server join as datasets grow larger, and
Oracle is suited to large-scale databases, that there would be
limitations here. Also, I tried to use Google to find evidence of this
feature in Oracle, and could not find it within a few minutes.

Regards,
Bill K.

[Malcolm Dew-Jones]

Bill Karwin (bi**@karwin.com) wrote:
: Malcolm Dew-Jones wrote:
: > Absolutely Oracle does this.
: >
: > A query can pull in tables from any (Oracle) database with the necessary
: > connections, and potentially other database via a supported gateway.
: >
: > This is not new. Oracle has done this for years.

: Fascinating, thanks for the info. I admit I have nearly zero experience
: with any version of Oracle. I was assuming that since it is potentially
: so costly to perform a cross-server join as datasets grow larger, and
: Oracle is suited to large-scale databases, that there would be
: limitations here. Also, I tried to use Google to find evidence of this
: feature in Oracle, and could not find it within a few minutes.

Unfortunately, definitive oracle info never seems to rise to the top of
any google searches.

However, google "Oracle select syntax" did bring up the following a little
ways down. It's not from oracle, but appears to be cut/paste from the
oracle docs.

http://www.cs.rpi.edu/~sibel/dbs/FAL.../sql_dmcmd.htm

SYNTAX:

SELECT [DISTINCT | ALL] { *
| { [schema.]{table | view | snapshot}.*
| expr } [ [AS] c_alias ]
[, { [schema.]{table | view | snapshot}.*
| expr } [ [AS] c_alias ] ] ... }
FROM [schema.]{table | view | subquery | snapshot}[@dblink]
[t_alias]
[, [schema.]... ] ...
[WHERE condition ]
[ [START WITH condition] CONNECT BY condition]
[GROUP BY expr [, expr] ... [HAVING condition] ]
[{UNION | UNION ALL | INTERSECT | MINUS} SELECT command ]
[ORDER BY {expr|position} [ASC | DESC]
[, {expr|position} [ASC | DESC]] ...]
[FOR UPDATE [OF [[schema.]{table | view}.]column
[, [[schema.]{table | view}.]column] ...] [NOWAIT] ]
Note the FROM clause and the "table@dblink"

The @dblink refers to a "database link" i.e. a connection to a remote
database server, and the syntax expression is showing that you can use it
as part of a select including multiple tables (i.e. a join).

as for

: Oracle is suited to large-scale databases

I would say that Oracle is suited to enterprise databases, that is to say,
databases that a business can depend on. Size has little to do with that.
And the efficiency is irrelevent in the sense that if you need the
functionality and it doesn't work then you have 0% efficiency. If you
need it a lot then there are ways to make things more efficient _if_ that
turns out to be a problem, such as local snapshots of remote data. Of
course Oracle costs more than some of the alternatives, so building your
own code for one task might well be cheaper, and is commonly more fun.

--

This space not for rent.