[DERBY-6267] Add ability to compactly specify a complete query plan in an optimizer override.

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.11.1.1
• Component/s: SQL
• Labels: derby_triage10_11
• Urgency: Normal
• Assignee: Unassigned
• Reporter: Richard N. Hillegas
• Created: 17/Jun/13 19:48
• Updated: 07/Mar/14 04:01
• Resolved: 06/Mar/14 13:17

Description

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.

Issue Links

• is related to: DERBY-6211 Make Optimizer trace logic pluggable.
• relates to: DERBY-6305 Tuning Derby information on optimizer overrides needs improvement