You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Rick Hillegas (JIRA)" <ji...@apache.org> on 2013/06/17 21:50:19 UTC
[jira] [Created] (DERBY-6267) Add ability to compactly specify a
complete query plan in an optimizer override.
Rick Hillegas created DERBY-6267:
------------------------------------
Summary: 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
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