Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    30

    Unanswered: Problem with optimizer creating cartesian product

    Greetings,

    I have a job that is getting hung up on what appears to be a simple statement. The following statement hangs when run however I pulled the statement out of the job and ran it and I could recreate the hanging situation, however when I recreate the objects and insert the same data into them and run in my test schema it runs fine. Any advice on where to look would be appreciated. Here's the statement, basically it's an interface between 2 systems that have different employee ids for the same person so we're using a reference table (view2) that contains both id's to join the two together.

    SELECT COUNT ('x')
    FROM schema1.ODS_NAMES PJ (view of ODS_PS_NAMES)
    INNER JOIN schema1.PSEMPNOREFERENCE P (view of ODS_PS_UC_EXT_SYSTEM) ON P.PSEMPNO = PJ.EMPLID
    INNER JOIN schema2.SYSTEMASSIGNMENT_ODS S ON S.EMPNO = P.EMPNO

    Here's the explain plan from when the process runs normally, appearing to create a cartesian product. It looks like it's taking the unrelated table and view and joining them without using the reference table that provides the join information.

    Plan
    1 Rows were retrieved by performing a fast read of all index records in schema2.PK_SYSTEMASSIGNMENT_ODS .
    2 Every row in the table schema1.ODS_PS_NAMES is read.
    3 BUFFER SORT
    4 Every row in step 1 was joined to every row in step 3.
    5 One or more rows were retrieved using index schema1.IDX_ODS_PS_NAMES_1 . The index was scanned in ascending order..
    6 Rows from table schema1.ODS_PS_NAMES were accessed using rowid got from an index.
    7 The rows were sorted to support a group operation (MAX,MIN,AVERAGE, SUM, etc).
    8 For the rows returned by step 7, filter out rows depending on filter criteria.
    9 VIEW PUSHED PREDICATE
    10 For each row retrieved by step 4, the operation in step 9 was performed to find a matching row.
    11 One or more rows were retrieved using index schema1.IDX_ODS_PS_UC_EXT_SYSTEM_1 . The index was scanned in ascending order..
    12 Rows from table schema1.ODS_PS_UC_EXT_SYSTEM were accessed using rowid got from an index.
    13 The rows from step 12 were sorted to eliminate duplicate rows.
    14 VIEW PUSHED PREDICATE
    15 For each row retrieved by step 10, the operation in step 14 was performed to find a matching row.
    16 The rows were sorted to support a group operation (MAX,MIN,AVERAGE, SUM, etc).
    17 Rows were returned by the SELECT statement.


    When I create all the tables and views and copy the records into them in a test schema, and run the same query, it returns results. The explain plan appears that it uses the reference table properly. So the question is why does the optimizer differ when run in test schema vs. the normal one and how can I correct it so the process joins correctly when run in the original schemas? Additional info -

    The table has the same index in both schemas, the tables under the views do not have any indexes on them (I know, I didn't design it).
    I rebuilt the index on the systemassignment_ods table in schema2, no difference.

    Plan
    1 Every row in the table schema1.ODS_PS_NAMES is read.
    2 HASH GROUP BY
    3 A view definition was processed, either from a stored view or as defined by steps 2.
    4 Every row in the table schema1.ODS_PS_NAMES is read.
    5 Every row in the table schema1.ODS_PS_UC_EXT_SYSTEM is read. <- this is the reference table joining the two properly
    6 HASH UNIQUE
    7 A view definition was processed, either from a stored view schema1.PSEMPNOREFERENCE or as defined by steps 6.
    8 Rows were retrieved using the unique index TESTUSER.SYSTEMASSIGNMENT_ODS_PK .
    9 For each row retrieved by step 7, the operation in step 8 was performed to find a matching row.
    10 The result sets from steps 4, 9 were joined (hash).
    11 The result sets from steps 3, 10 were joined (hash).
    12 The rows were sorted to support a group operation (MAX,MIN,AVERAGE, SUM, etc).
    13 Rows were returned by the SELECT statement.

    Any help or direction would be greatly appreciated.

    Andrew

  2. #2
    Join Date
    Jan 2004
    Posts
    30
    Update: The query run in the test schema was this...so I am joining the original views with the table created in the new view.

    SELECT COUNT ('x')
    FROM schema1.ODS_NAMES PJ (view of ODS_PS_NAMES)
    INNER JOIN schema1.PSEMPNOREFERENCE P (view of ODS_PS_UC_EXT_SYSTEM) ON P.PSEMPNO = PJ.EMPLID
    INNER JOIN testuser.SYSTEMASSIGNMENT_ODS S ON S.EMPNO = P.EMPNO

  3. #3
    Join Date
    Oct 2007
    Posts
    162
    Provided Answers: 9
    just a quick note for you, the optimizer does not cause a Cartesian product. Leaving off join conditions between tables is what causes them.

  4. #4
    Join Date
    Aug 2017
    Posts
    6
    It seems Oracle optimizer made different execution plan in different environment, you can use Hints to influence Oracle optimizer. In your case, Hash join may be good.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,993
    Provided Answers: 23
    Please show us the real execution plan.
    Code:
    Pre-formatted text please
    using [code] tags otherwise the very important indention is los.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

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