[jira] [Updated] (DERBY-6267) Add ability to compactly specify a complete query plan in an optimizer override.

["Rick Hillegas (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-6267?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas updated DERBY-6267:
---------------------------------

    Attachment: derby-6267-01-ad-compactSyntax.diff

Attaching derby-6267-01-ad-compactSyntax.diff. This patch introduces support for compact, fully-specified plan shapes for simple SELECTs. I am running tests now.

This patch makes several changes:

1) Introduces a new class, OptimizerPlan. This is a graph of join nodes and row sources, representing the shape of a plan being considered by the optimizer. If the graph is bushy, we raise an error at bind() time today. However, this structure may be useful when we implement bushy trees in the future. The graph does not contain any directives about where to attach predicates or add/eliminate sorts. It is assumed that those decisions are a mechanical process. Placement of predicates and sort addition/elimination are left to the cost machinery and to the code generator. OptimizerPlan lives in an interface package although it contains a lot of implementation code. I put it in an interface package because some day it may be useful for users who want to implement alternative optimizers for Derby. An alternative optimizer would be a module which processes ASTs and produces OptimizerPlans. The plans would then be passed (as overrides) to the basic Derby optimizer, which would mechanically translate them into actual runtime execution structures.

Errors are raised at bind() time if the OptimizerPlan doesn't have the correct number of row sources, if the row source names can't be bound to conglomerates/tableFunctions, or if the plan isn't left-deep.

2) Adds new syntax to the Derby grammar, allowing the specification of complete query plans. A complete query plan is a comment line beginning with the literal "--derbyplan". The plan itself is a parenthesized sequence of row source names (the schema-qualified names of conglomerates and table functions) and infix join operator symbols. The plan specifications are what appear in the summaries of xml-based optimizer traces. So for instance:

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
--derbyplan ( ((SYS.SYSSEQUENCES_INDEX2 # SYS.SYSCOLUMNS_HEAP) # SYS.SYSALIASES_INDEX1) # SYS.SYSTABLES_INDEX1 )
;

or

select columnname from sys.syscolumns, table( integerList() ) i\n
where columnnumber = -i.a
--derbyplan ( sys.syscolumns_heap * app.integerList() )

3) Adds new tests for this functionality.


More work needs to be done, including:

A) Complete plan overrides for SELECTs with multiple query blocks.

B) More tests.

C) Documenting the new syntax.



Touches the following files:

-----------------

A       java/engine/org/apache/derby/iapi/sql/compile/OptimizerPlan.java

The work described in (1) above.

-----------------

M       java/engine/org/apache/derby/iapi/sql/StatementUtil.java
M       java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java

I decided that it was better not to model OptimizerPlan as an AST node graph, for the following reasons:

i) That would involve bringing a lot of implementation machinery over to the interface side.

ii) I wanted a clean separation between parser and optimizer data structures.

However, row source names in the OptimizerPlan need to be bound. So I moved TableName binding out of QueryTreeNode into StatementUtil, where it can be used by OptimizerPlan and AST nodes.

-----------------

M       java/engine/org/apache/derby/iapi/sql/compile/OptimizerFactory.java
M       java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
M       java/engine/org/apache/derby/impl/sql/compile/DistinctNode.java
M       java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
M       java/engine/org/apache/derby/impl/sql/compile/GroupByNode.java
M       java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java
M       java/engine/org/apache/derby/impl/sql/compile/OptimizerFactoryImpl.java
M       java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
M       java/engine/org/apache/derby/impl/sql/compile/CurrentOfNode.java
M       java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java
M       java/engine/org/apache/derby/impl/sql/compile/TableOperatorNode.java
M       java/engine/org/apache/derby/impl/sql/execute/BasicNoPutResultSetImpl.java
M       java/engine/org/apache/derby/impl/sql/compile/SingleChildResultSetNode.java

Small, mechanical changes needed to add OptimizerPlan to the optimizer interface.

-----------------

M       java/engine/org/apache/derby/impl/sql/compile/XMLOptTrace.java

Moved some code into OptimizerImpl so that it doesn't have to be duplicated by xml-based tracing.

-----------------

M       java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
M       java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj

Grammar and bind-time work to support complete plan overrides for simple SELECTs.

-----------------

M       java/engine/org/apache/derby/impl/sql/compile/OptimizerImpl.java

Support for complete plan overrides in the optimizer. This turned out to be a fairly small amount of code, mostly isolated in the method which chooses the next decoration for a slot in the join order. (A decoration is a conglomerate plus instructions on how the slot joins to its predecessor in the join order.) The new code loops through decorations until it finds the one which matches what the user specified for that slot in the join order.

-----------------

M       java/engine/org/apache/derby/impl/sql/execute/VTIResultSet.java

Added a toXML() method so that table functions would produce more detailed xml traces (see DERBY-6266).

-----------------

M       java/engine/org/apache/derby/loc/messages.xml
M       java/shared/org/apache/derby/shared/common/reference/SQLState.java

New and changed error messages.

-----------------

M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
A       java/testing/org/apache/derbyTesting/functionTests/tests/lang/NewOptimizerOverridesTest.java
A       java/testing/org/apache/derbyTesting/functionTests/tests/lang/resultSetReader.policy
M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

Initial tranche of tests for this feature.

                
> Add ability to compactly specify a complete query plan in an optimizer override.
> --------------------------------------------------------------------------------
>
>                 Key: DERBY-6267
>                 URL: https://issues.apache.org/jira/browse/DERBY-6267
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.11.0.0
>            Reporter: Rick Hillegas
>              Labels: derby_triage10_11
>         Attachments: derby-6267-01-ac-compactSyntax.diff, derby-6267-01-ad-compactSyntax.diff
>
>
> 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.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira