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, 23 Jul 2013 14:20:48 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-04-aa-fetchOffsetTest.diff

Attaching derby-6267-04-aa-fetchOffsetTest.diff. This patch adds test cases for compact optimizer
overrides with queries which have FETCH/OFFSET clauses.

At first blush, the placement of the optimizer override may look a little awkward when the
query contains a FETCH/OFFSET clause. That is because the FETCH/OFFSET clause appears after
the actual query specification. The optimizer override is placed after all of the other clauses
but before the FETCH/OFFSET clause. I considered changing the syntax so that the optimizer
override would appear as part of the FROM list. But after more thought, that didn't seem any
less arbitrary.

Here's what an optimizer override looks like for a statement with one query block and a FETCH/OFFSET
clause:

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 )
fetch first 1 rows only

...and here's what an optimizer override looks like for a statement with two query blocks:

select tableid
from sys.systables t
where tableid =
(
    select referenceid from sys.syscolumns where referenceid = t.tableid and 1=2
    --derbyplan sys.syscolumns_index1
    fetch first 1 rows only
)
--derbyplan sys.systables_heap



Touches the following file:

M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/NewOptimizerOverridesTest.java

                
> 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,
derby-6267-01-ae-compactSyntax.diff, derby-6267-02-aa-moreTests.diff, derby-6267-03-aa-moreSubqueryTests.diff,
derby-6267-04-aa-fetchOffsetTest.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