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 Wed, 03 Jul 2013 15:00:26 GMT

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

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

Thanks Rick,

It's clear that you understand my concerns since you expressed them yourself in
your response. 

And I'm in 100% agreement with the statements and observations you made.

My uneasiness arises partly from the concern that impatient users won't use
this as a short-term workaround while they help us improve the optimizer; they'll
instead install their --derbyplan override as a permanent feature of their
application, and we'll never hear from them again (until they discover that they
didn't specify the right --derbyplan :)  )

And my uneasiness also arises from the fear that this is a slippery slope: 
applications won't stop with just micro-managing the join order for a query.

The applications (more precisely, the application authors) want to specify and
control things like:

- don't take any locks on table PRICELIST; I promise it's not being changed.

- don't write any transaction log records for table ORDER_HIST; I promise I'll
  never want to recover it. And you don't need to flush the data pages to disk, either.

- when you insert a row into table USER_DATA, make sure the page has an
  extra 500 bytes of free space; I'm going to be updating it later in my application,
  and I need some free space to be available

- use round-to-lowest rather than round-to-zero when inserting floating point data

- make sure you have at least 100 MB of free memory before you run this query

And soon our simple little language for controlling join order in a --derbyplan
comment has become a complete programming language of its own.

I think I'm just expressing my discomfort at the fact that Derby, which has
the GREAT virtue of simplicity and clarity, jealously preserved at great efforts
over multiple decades, is inevitably going to face the harsh reality of real life.

So to summarize: Yes, I agree with you. And I'm also nervous about the overall
future and vision of the software, as it continues to grow and mature. As I know
you are, as well.

Thanks for having the discussion, it was quite valuable to me!

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