How to use Oracle and PostgreSQL query in one select query

Question

In my Java program, I am trying to take values from a PostgreSQL database and using this data I am using a Select query with an Oracle database.

Problem is, it is taking too much time to complete this task. First I am fetching data from Postgres table and load into variable.

Then with this variable I am executing a select query against an Oracle table.

But I want to make this process faster. Is it possible to perform this task in one query that takes data from PostgreSQL table and fetch data from Oracle table?

Postgres statement:

select filial_name 
  into f_name 
from branch 
where id=1;

Oracle statement:

select sum(credit)  
from balance 
where filial_n = f_name;

Above process continues in loop.

Seems there's a way to create a dblink from oracle to pg : community.oracle.com/thread/4176489 - you should give more detail on what youre trying to do though. "I'm trying to JOIN a massive amount of data in PG to a massive amount of data in Oracle and it's taking long" isn't really good enough in terms of the detail we need to help you — Caius Jard
– Caius Jard, Commented Aug 9, 2019 at 5:53

Laurenz Albe · Accepted Answer · 2019-08-09 06:01:48Z

1

If you have to run a massive join between an Oracle table and a PostgreSQL table, that is never going to be very fast.

But you can do much better than performing the join in your application by defining an oracle_fdw foreign table in PostgreSQL and performing the join in PostgreSQL.

answered Aug 9, 2019 at 6:01

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use Oracle and PostgreSQL query in one select query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related