join between tables in different connections

Hi,

I have a connection to an oracle database from php, in that database i have many tables and i  make inner join between them, my problem is that i have one database with tables in another location, and i need to access these tables and make inner joins with them

I am using the obi_parse to make the query and obi_connect to make the connection
LVL 1
joyacv2Asked:
Who is Participating?
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
Do your Oracle databases have a "database link" defined that allows a connection one of them to query data from the other one also?  If yes, add the link name to the table.  For example, if you need to query "customer" data from the Oracle database you connect to and "order" data from a second (or remote) database, your query woule look something like this:

select [columns]
from customer c, order@remotedb o
where o.customer_id = c.customer_id
0
 
SlimshaneeyCommented:
This isnt a good idea for a number of reasons, not least because of performance. Even if you COULD do joins across multiple connections, there would be no indexing or anything that would speed up the connections. Your best bet would be to create a view to the remote table (and thus make the connection via Oracle and not PHP), and join that way. The performance would be significantly better.

S
0
 
joyacv2Author Commented:
Hi,

But how i can do the query?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
SlimshaneeyCommented:
When you create a view in Oracle (as in any other SQL compliant DB) the view acts almost like a table itself. So you would create joins just as you would any other query. The tricky bit is configuring Oracle to do the remote table linking to make the view.
0
 
Mark GeerlingsDatabase AdministratorCommented:
Whenever you ask Oracle to join tables from different databases, you risk having a big performance penalty.  It doesn't matter if you do the join directly in your client tool, or if you create view in one of the Oracle databases to do the join to the other database for you.
0
 
joyacv2Author Commented:
Hi,

But how i can do the query?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.