Left Join wrong transformation using Oracle Heterogeneous Service

Question

I have an Oracle Database connected using DB link with remote DB. My remote DB has nothing to do with OUTER JOINs, that's why Heterogeneous Service transforms my query to several simple queries and concatenates results.

For example I have 3 tables:

create table join_email
(
EMAIL_ID FLOAT(10)
SENDER VARCHAR2(128)
)
INSERT INTO join_email VALUES (1,'bmdrrfh@gmail.com')
INSERT INTO join_email VALUES (2,'n3qcd@gmail.com')

create table join_email_receivers
(
EMAIL_ID FLOAT(10)
RECEIVER VARCHAR2(128)
)
INSERT INTO join_email_receivers VALUES (1,'9wtcptyzn@yahoo.com')
INSERT INTO join_email_receivers VALUES (1,'8w7o5@yahoo.com')
INSERT INTO join_email_receivers VALUES (1,'jlwtc@yahoo.com')
INSERT INTO join_email_receivers VALUES (2,'fpm@yandex.ru')
INSERT INTO join_email_receivers VALUES (2,'a@mail.com')

create table join_email_cc
(
EMAIL_ID FLOAT(10)
CC VARCHAR2(128)
)
INSERT INTO join_email_cc VALUES (1,'rg1yzjc@mail.com')

I want to query rows from the 1 table and left join rows from 2nd and 3rd by email_id. My query looks like:

select em.sender, emr.receiver, emcc.cc
from join_email@DG4 em
LEFT JOIN join_EMAIL_RECEIVERS@DG4 emr on emr.email_id=em.email_id
LEFT JOIN join_EMAIL_CC@DG4 emcc on emcc.email_id=em.email_id
where em.sender = 'bmdrrfh@gmail.com' and emr.receiver = '9wtcptyzn@yahoo.com';

The problem is Heterogeneous Service transforms this query to two following queries:

<SELECT A2."EMAIL_ID",A2."SENDER",A1."RECEIVER" FROM "JOIN_EMAIL" A2,"JOIN_EMAIL_RECEIVERS" A1 WHERE A1."EMAIL_ID"=A2."EMAIL_ID" AND A2."SENDER"='bmdrrfh@gmail.com' AND A1."RECEIVER"='9wtcptyzn@yahoo.com'>

and

<SELECT "CC","EMAIL_ID" FROM "JOIN_EMAIL_CC">

The 2nd query is a FULL SCAN query that is wrong, it should be a WHERE clause by email_id.

My question is, how to tell Heterogeneous Service how to transform my query in a right order?

Answer 1

The first LEFT OUTER JOIN is in fact an INNER JOIN because of the equality predicate on the column emr.receiver, therefore it is correctly transformed by Oracle.

If the RDBMS running on the remote site natively supports outer joins, you could create a view on the remote site leaving out the WHERE clause, e.g.

On the remote site (DG4 in your example):

create view my_view as
select em.sender, emr.receiver, emcc.cc
from join_email em
LEFT JOIN join_EMAIL_RECEIVERS emr on emr.email_id=em.email_id
LEFT JOIN join_EMAIL_CC emcc on emcc.email_id=em.email_id

And then on the local site:

select *
  from my_view@DG4
 where em.sender = 'bmdrrfh@gmail.com'
   and emr.receiver = '9wtcptyzn@yahoo.com'