Solved

join between tables in different connections

Posted on 2014-03-31
6
528 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Generate PDF from MySQL using PHP 3 52
Unwanted output from my query 5 39
PHP function parameters defined 14 38
Generate PDF's using TCPDF - Initial Set Up 9 29
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

680 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