Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Oct 2014
    Posts
    12

    Unanswered: Left Join wrong transformation using Oracle Heterogeneous Service

    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:
    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:

    Code:
    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:

    Code:
    <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
    Code:
    <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?

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    what are the two databases type?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Oct 2014
    Posts
    12
    Quote Originally Posted by beilstwh View Post
    what are the two databases type?
    As stated above Oracle Database and "homemade" database which does not support OUTER JOIN.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by valgussev View Post
    As stated above Oracle Database and "homemade" database which does not support OUTER JOIN.
    You mean that you actually wrote your own database engine. That's pretty good.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Oct 2014
    Posts
    12
    The problem is I do not get an idea why Oracle Heterogeneous Service implements one of those 2 queries as a full scan and how to make a workaround.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    and since you refuse to name what your ""homemade" database" is then there is no way to know. im out of this thread
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Oct 2014
    Posts
    12
    The name of this database can't be found in the internet. Just imagine it is whatever database, that can't implement OUTER JOINs but can be connected using ODBC and Heterogeneous Service with Oracle DB.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    so explain again why you think this is an Oracle problem and not a design limitation with this other unnamed "database"?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Oct 2014
    Posts
    12
    That is interesting, what kind of design limitation you meant? I decided the problem on the Oracle side because I see how the queries looks like when they coming from Oracle to unknown DB.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by valgussev View Post
    That is interesting, what kind of design limitation you meant? I decided the problem on the Oracle side because I see how the queries looks like when they coming from Oracle to unknown DB.
    then file bug report with Oracle.
    nobody here can change your reality.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Oct 2014
    Posts
    12
    Quote Originally Posted by anacedent View Post
    then file bug report with Oracle.
    nobody here can change your reality.
    Fine, tell me which exactly information you need about unknown DB to change my reality.

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    For the sake of this discussion that you are correct.
    So where does that leave you?

    then file bug report with Oracle.
    nobody here can change your reality.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Oct 2014
    Posts
    12
    Quote Originally Posted by anacedent View Post
    For the sake of this discussion that you are correct.
    So where does that leave you?

    then file bug report with Oracle.
    nobody here can change your reality.
    Am I told somewhere in this discussion that I am 100% right? If I thought so I've never ask such a question here.
    Don't you came here to show your knowledge and help or just to talk about reality?

  14. #14
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by valgussev View Post
    The 2nd query is a FULL SCAN query that is wrong, it should be a WHERE clause by email_id.
    There cannot be a WHERE clause by email_id, because you do not supply email_id as a search argument. There is a join on email_id, and since according to you the data source cannot perform this outer join all rows must be sent back to the Oracle side where the join is performed.
    ---
    "It does not work" is not a valid problem statement.

  15. #15
    Join Date
    Oct 2014
    Posts
    12
    Quote Originally Posted by n_i View Post
    There cannot be a WHERE clause by email_id, because you do not supply email_id as a search argument. There is a join on email_id, and since according to you the data source cannot perform this outer join all rows must be sent back to the Oracle side where the join is performed.
    The first query looks like
    Code:
    <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'>
    email_id, sender and receiver will be an output of such query. It should be faster to get the email_id and implement the 2nd query with WHERE clause than to perform a full scan. Next perform an OUTER JOIN on the Oracle side.

    In my case, email_id is just a way to connect all 3 tables. When I perform a query, I know only receiver, sender or cc, I don't know any information about email_id.

    If you think in this case Full Scan should be performed anyway, could you please suggest me another query without Full Scan?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •