Solved

join between tables in different connections

Posted on 2014-03-31
6
519 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
  • 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 500 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

930 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now