Uploaded image for project: 'Apache Trafodion'
  1. Apache Trafodion
  2. TRAFODION-3050

Inefficient plan when joining on VARCHAR columns

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.3
    • Fix Version/s: 2.3
    • Component/s: sql-cmp
    • Labels:
      None

      Description

      This is follow-on work to Jira TRAFODION-3031. That Jira fixed some issues with plan choice when joining on integer columns.

      In this particular example, the join is on VARCHAR columns. With the fix from Jira TRAFODION-3031, we get a nice nested join plan for the subqueries, but still get a hash join at the top, which is inefficient in this example.

      The attached script reproduces the problem and demonstrates a work-around. When CQD COMP_BOOL_158 is set to 'ON', we get a good plan. The attached log shows the script results.

        Attachments

        1. JiraVarchar.log
          8 kB
          Dave Birdsall
        2. JiraVarchar.sql
          2 kB
          Dave Birdsall

          Issue Links

            Activity

              People

              • Assignee:
                dbirdsall Dave Birdsall
                Reporter:
                dbirdsall Dave Birdsall
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: