db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-6267) Add ability to compactly specify a complete query plan in an optimizer override.
Date Tue, 02 Jul 2013 15:34:20 GMT

     [ 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

Mime
View raw message