Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-4339

improve sort avoidance algorithm to include equijoin relationships

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal
    • Bug behavior facts:
      Performance

      Description

      Not all sort avoidance paths are recognized by the optimizer. Here is an example of the class targeted by this issue, from
      the wisc tests :

      select * from --DERBY-PROPERTIES joinOrder=FIXED
      TENKTUP2, TENKTUP1
      where TENKTUP1.unique1 = TENKTUP2.unique1
      and TENKTUP2.unique1 < 2500
      order by TENKTUP1.unique1';

      There is a unique index on TENKTUP1.unique1 and TENKTUP2.unique1. To exercise the path, the plan
      forces TENKTUP2 as outermost join, knows that query result is sorted on TENKTUP2.unique1, but does not
      recognize that because "TENKTUP1.unique1 = TENKTUP2.unique1" that query is also sorted on TENKTUP1.unique1 and could avoid a sort.

      Note without the fixed join order a sort avoidance plan is picked by choosing the plan where TENKTUP1.unique1 is the
      outermost node.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                mikem Mike Matrigali
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated: