[
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
