Hi Mike,
This is an attempt to answer your questions based on my recent work in
this code area. Other people may know more than I do.
On 7/25/13 8:41 AM, mike matrigali wrote:
Is there an optimizer override in 10.8 to force a multi-probe query plan?
Not that I'm aware of. Some of the optimizer overrides are documented in
the Tuning Guide section titled "Overriding the default optimizer
behavior". Other overrides are not documented, at least as far as I can
see. The --derby-properties overrides are supposed to be vetted by the
implementations of Optimizable.verifyProperties(). The following
overrides are vetted by these implementations:
FromBaseTable.verifyProperties():
index
constraint
joinStrategy
hashInitialCapacity
hashLoadFactor
hashMaxCapacity
bulkFetch
FromTable.verifyProperties():
joinStrategy
hashInitialCapacity
hashLoadFactor
hashMaxCapacity
If not, is there an override in the new work being done in trunk to
force it?
No. The new --derbyplan override merely provides a way to compactly
specify access paths and join strategies.
Also if not, is there any suggestion of what the right syntax would be
to add it to the old style optimizer override?
I believe that Bryan and I would prefer to keep the new compact plan
overrides simple. My recommendation would be to use the old-style
--derby-properties for this purpose.
If I correctly understand your goal, you are trying to force the use of
a MultiProbeTableScanResultSet. According to DERBY-2503, this technique
is only allowed for NestedLoop joins right now. Maybe the following new
--derby-properties property (handled by FromBaseTable and FromTable)
would capture what you need to specify. This could be specified anywhere
that the index and constraint access paths are specified. According to
the Tuning Guide, that would be at the end of a table expression:
multiProbe=true
You may get better advice from people who are more familiar with the
old-style --derby-properties.
Hope this helps,
-Rick
It seems recently there have been a number of issues where user
expected multi-probe plan but for various reasons the system has
chosen another plan. Some of these have been bugs, some of these
have been statistics out of date, and most recently the application
would prefer multi-probe for more concurrency even if the execution
is less efficient than full scan.
/mikem
