Details

    • Steps to Reproduce:
      Hide

      Three databases are involved with joins that chain them together. A makedep option is given for the lean table of the second and third databases. If the second database is outer joined, no dependent join is created for it.

      Show
      Three databases are involved with joins that chain them together. A makedep option is given for the lean table of the second and third databases. If the second database is outer joined, no dependent join is created for it.
    • Workaround Description:
      Hide

      Use a preserve hint to prevent the manipulation of the join ordering. In this example it can be applied around the top level join:

      ... FROM /*+ preserve */ (meds_owner.compound ...) WHERE MEDS_OWNER... 
      Show
      Use a preserve hint to prevent the manipulation of the join ordering. In this example it can be applied around the top level join: ... FROM /*+ preserve */ (meds_owner.compound ...) WHERE MEDS_OWNER...

      Description

      This query:

      Edit: <I was asked to remove the query text from the top level description>
      The query is between three different databases chained together with joins (see plans).

      does not create a dependent join on the middle table. If you change the join to the middle table to inner, it does.

      Plans and debug plans for both cases are attached. As a note, an older version of Teiid that we are also using (9.3.4) always creates the dependent join. 11.0.1 and 10.2.3 do not create the dependent join if the table is outer joined.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                mike_higgins Mike Higgins
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: