?
Solved

join between tables in different connections

Posted on 2014-03-31
6
Medium Priority
?
534 Views
Last Modified: 2014-03-31
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
0
Comment
Question by:joyacv2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 39966696
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
 
LVL 1

Author Comment

by:joyacv2
ID: 39966735
Hi,

But how i can do the query?
0
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 39966807
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39966838
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
 
LVL 1

Author Comment

by:joyacv2
ID: 39966858
Hi,

But how i can do the query?
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 2000 total points
ID: 39966964
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

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question