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

Add ability to compactly specify a complete query plan in an optimizer override.

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.11.1.1
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal

      Description

      It would be nice to be able to override the optimizer's choice and specify a complete query plan using the compact summary syntax output by XMLOptTrace. Given how the optimizer handles a statement, this would require binding a query plan at the query block level. Two obvious candidates for such a feature are:

      1) Extend the use of DERBY-PROPERTIES in the comments of a query.

      2) Add an extra clause to query blocks. The clause would have to be a clearly marked Derby extension.

      (1) might look like this (here we add a new "fullQueryPlan" property):

      select tablename from sys.systables t, sys.syscolumns c, sys.sysaliases a
      where t.tablename = c.columnname and c.columnname = a.alias
      – DERBY-PROPERTIES fullQueryPlan = (SYSCOLUMNS_HEAP # SYSALIASES_INDEX1) # SYSTABLES_INDEX1
      union all
      select tablename from sys.systables t, sys.syscolumns c, sys.sysaliases a, sys.syssequences s
      where t.tablename = c.columnname and c.columnname = a.alias and a.alias = s.sequencename
      – DERBY-PROPERTIES fullQueryPlan = ((SYSCOLUMNS_HEAP # SYSTABLES_INDEX1) # SYSALIASES_INDEX1) # SYSSEQUENCES_INDEX2
      ;

      (2) might look like this (here we add a new "using derby join order" clause):

      select tablename from sys.systables t, sys.syscolumns c, sys.sysaliases a
      where t.tablename = c.columnname and c.columnname = a.alias
      using derby join order (SYSCOLUMNS_HEAP # SYSALIASES_INDEX1) # SYSTABLES_INDEX1
      union all
      select tablename from sys.systables t, sys.syscolumns c, sys.sysaliases a, sys.syssequences s
      where t.tablename = c.columnname and c.columnname = a.alias and a.alias = s.sequencename
      using derby join order ((SYSCOLUMNS_HEAP # SYSTABLES_INDEX1) # SYSALIASES_INDEX1) # SYSSEQUENCES_INDEX2
      ;

      Here's a comparison of these approaches:

      (1)
      + Portability: the same query text can be used against different RDBMSes.

      • Parsing of DERBY-PROPERTIES happens outside the grammer.

      (2)
      + Parsing happens in the parser.

      • Not portable.

      I slightly prefer approach (1). If I pursue that approach, I would like to see if I can move the parsing into the parser.

      I am interested in other opinions about how to address this feature. Thanks.

        Attachments

        1. derby-6267-04-aa-fetchOffsetTest.diff
          3 kB
          Richard N. Hillegas
        2. derby-6267-03-aa-moreSubqueryTests.diff
          6 kB
          Richard N. Hillegas
        3. derby-6267-02-aa-moreTests.diff
          14 kB
          Richard N. Hillegas
        4. derby-6267-01-ae-compactSyntax.diff
          91 kB
          Richard N. Hillegas
        5. derby-6267-01-ad-compactSyntax.diff
          91 kB
          Richard N. Hillegas
        6. derby-6267-01-ac-compactSyntax.diff
          52 kB
          Richard N. Hillegas
        7. DERBY-6267_classespolicy.diff
          0.7 kB
          Myrna van Lunteren

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                rhillegas Richard N. Hillegas
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: