Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2007
    Posts
    6

    Red face Unanswered: Need help with Catesian catastrophe...

    I'm using TOAD at the moment to try and retrieve 1 row of information extracted from 2 individual tables. Below is my code:

    Code:
    SELECT 
            tableA_pidm "PIDM", 
            tableA_first_name "First Name", 
            tableA_mi "Middle Initial",
            tableA_last_name "Last Name",
            tableB_street_line1 "Street",
            tableB_city "City",
            tableB_stat_code "State",
            tableB_zip "Zip"
    FROM 
            tableA
    INNER JOIN tableB on tableA_pidm = 1672
    WHERE
            tableB_pidm = tableA_pidm
    AND  tableA_change_ind IS NULL 
    AND  tableB_atyp_code = 'MA' 
    AND  tableB_changed IS NULL
    AND  SYSDATE BETWEEN tableB_from_date AND NVL(tableB_to_date,SYSDATE+1);
    Here's what I need: 1 single row of information with the ID, first name, middle name, last name, street, city, state, and finally, zip. It has to be the most recent in the tables, too (the most up-to-date, recent row of information).

    So judging from the above provided, can anyone around here tell me why a Cartesian product is happening? I'm obviously new to this type of SQL and using SQL in general. Hell, I never even knew what a Cartesian product was until I started creating them! Ha!

    Any help on this is greatly appreciated.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It does not appear to me to be a Cartesian Product.
    post results from below
    Code:
    SELECT COUNT(*)
    FROM 
            tableA
    INNER JOIN tableB on tableA_pidm = 1672
    WHERE
            tableB_pidm = tableA_pidm
    AND  tableA_change_ind IS NULL 
    AND  tableB_atyp_code = 'MA' 
    AND  tableB_changed IS NULL
    AND  SYSDATE BETWEEN tableB_from_date AND NVL(tableB_to_date,SYSDATE+1);
    post DDL (CREATE TABLE) for both tables
    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.

  3. #3
    Join Date
    Jan 2007
    Posts
    6
    anacedent, it returned 1 and still had the "Explain Plan" notice about the "Merge JOIN Cartesian".

    That said, the first step seems to be "INDEX RANGE SCAN INDEX" of tableB_key_index2.

    The second step is TABLE ACCESS BY INDEX ROWID TABLE tableB.

    The third step is INDEX RANGE SCAN INDEX (UNIQUE) tableA_key_index.

    The fourth step is BUFFER SORT.

    The fifth and final step is where I see the information regarding the "MERGE JOIN CARTESIAN".

    When I highlight that fifth step entry on the Explain Plan output, it says that "Every row in step 2 was joined to every row in step 4."

    As far as the DDL is concerned, I'll try to include everything I can:

    tableA:
    TABLEA_PIDM
    TABLEA_LAST_NAME
    TABLEA_FIRST_NAME
    TABLEA_MI
    TABLEA_CHANGE_IND
    tableB:
    TABLEB_PIDM
    TABLEB_ATYP_CODE
    TABLEB_FROM_DATE
    TABLEB_TO_DATE
    TABLEB_STREET_LINE1
    TABLEB_CITY
    TABLEB_STAT_CODE
    TABLEB_ZIP
    TABLEB_CHANGED
    Hope this helps because I probably can't provide much more than this--system security and such. Let me know if there's anything else I can do and thanks for your help.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The fifth and final step is where I see the information regarding the "MERGE JOIN CARTESIAN".
    >When I highlight that fifth step entry on the Explain Plan output, it says that "Every row in step 2 was joined to every row in step 4."

    Ignore these message. The SQL is OK.
    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.

Posting Permissions

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