By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 909 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Join across servers

P: n/a
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
Jul 23 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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
Jul 23 '05 #2

P: n/a
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
Jul 23 '05 #3

P: n/a
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.
Jul 23 '05 #4

P: n/a
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
Jul 23 '05 #5

P: n/a
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.
Jul 23 '05 #6

P: n/a
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.
Jul 23 '05 #7

P: n/a
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.
Jul 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.