473,225 Members | 1,317 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,225 software developers and data experts.

Join across servers

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
7 11161
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Meghna | last post by:
Hi, I was wondering if some one would be able to help me with this. I am trying to share a session across two servers and I seem to be experiencing some problems. I was wondering if anybody has...
2
by: djc | last post by:
I need to use data from multiple tables that are located in 2 different databases. Most of the tables are in SQL Server 2000 but 1 one the tables I need to include in my query resides in an...
3
by: Ike | last post by:
Oh I have a nasty query which runs incredibly slowly. I am running MySQL 4.0.20-standard. Thus, in trying to expedite the query, I am trying to set indexes in my tables. My query requires four...
2
by: Preston Landers | last post by:
Hello all. I am trying to write a query that "just" switches some data around so it is shown in a slightly different format. I am already able to do what I want in Oracle 8i, but I am having...
2
by: terence.parker | last post by:
I am often faced with the dilemma of whether to use a JOIN query across three tables in order to grab a bunch of results - or whether to create another table to represent what I want. The latter is...
2
by: Christopher D. Wiederspan | last post by:
We are getting ready to move an ASP.NET application off of a single development machine and onto a "webfarm". Basically our webfarm is a bunch of identical servers with the load-balancing provided...
2
by: Chris Fink | last post by:
What is the recommendation for sharing master pages across IIS Servers? In our corporation, we have many webservers in different environments (intranet, extranet, etc). I've done some searching,...
10
by: Br | last post by:
We have a product that is being used by a client to outsource salary packaging. They therefore run several instances of our database, one for each of their clients. There is now a requirement to...
0
by: cherryblossom | last post by:
hii, i am developing a client server application in C#.. In this multiple clients can connect 2 server. I am using a set of 4 servers so that the fault tolerance can be increased in case a...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.