1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Problem with Left Join transformation using Oracle Heterogeneous Service

Discussion in 'General' started by valgussev, Oct 29, 2014.

  1. valgussev

    valgussev Guest

    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:

    Code (Text):
        create table join_email
        EMAIL_ID FLOAT(10)
        SENDER VARCHAR2(128)
        INSERT INTO join_email VALUES (1,'bmdrrfhmail')
        INSERT INTO join_email VALUES (2,'n3qcdmail')
        create table join_email_receivers
        EMAIL_ID FLOAT(10)
        RECEIVER VARCHAR2(128)
        INSERT INTO join_email_receivers VALUES (1,'9wtcptyznmail')
        INSERT INTO join_email_receivers VALUES (1,'8w7o5mail')
        INSERT INTO join_email_receivers VALUES (1,'jlwtcmail')
        INSERT INTO join_email_receivers VALUES (2,'fpm mail')
        INSERT INTO join_email_receivers VALUES (2,'amail')
        create table join_email_cc
        EMAIL_ID FLOAT(10)
        CC VARCHAR2(128)
        INSERT INTO join_email_cc VALUES (1,'rg1yzjcmail')
    I want to query rows from the 1 table and left join rows from 2nd and 3rd by email_id.
    My query looks like:

    Code (Text):
     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 = 'bmdrrfhmail' and emr.receiver = '9wtcptyznmail';
    The problem is Heterogeneous Service transforms this query to two following queries:

    Code (Text):
    Code (Text):
    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?