Solved

join between tables in different connections

Posted on 2014-03-31
6
532 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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
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 …

717 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