db-derby-dev mailing list archives

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

    [ https://issues.apache.org/jira/browse/DERBY-6267?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13698358#comment-13698358
] 

Bryan Pendleton commented on DERBY-6267:
----------------------------------------

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

Hi Rick,

In some ways I think this is pretty cool.

In other ways I think: urk!

I'm pinching myself, thinking: this sort of thing is just what I hate 
about other database implementations.

I *LOVE* all the improved visibility into the optimizer's behavior.

I cringe at the new weird and strange ways to twist knobs and try
to control the Derby engine.

I sort of feel like we're in danger of becoming like one of those
people who has adopted a dog from the shelter and now doesn't
know how to handle it: instead of focusing our attention and efforts
on making the optimizer transparent and improving it, we're building
large chain-link leashes that we can shackle around the optimizer
and force it to do our bidding.

Am I making any sense?

thanks,

bryan

                
> 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
>
>
> 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