I believe I have seen overrides that specify the Derby property
"derby.storage.pageSize"
is there are long columns, cf. this code in CreateTableNode (which uses
properties collected from --DERBY-PROPERIES):
if (table_has_long_column || (approxLength >
Property.TBL_PAGE_SIZE_BUMP_THRESHOLD))
{
if (((properties == null) ||
(properties.get(Property.PAGE_SIZE_PARAMETER) == null)) &&
(PropertyUtil.getServiceProperty(
getLanguageConnectionContext().getTransactionCompile(),
Property.PAGE_SIZE_PARAMETER) == null))
{
// do not override the user's choice of page size,
whether it
// is set for the whole database or just set on this
statement.
if (properties == null)
properties = new Properties();
properties.put(
Property.PAGE_SIZE_PARAMETER,
Property.PAGE_SIZE_DEFAULT_LONG);
}
}
That is, we the user specified a value, use it, else use default. I love
would for all the possible usages ofr --DERBY-PROPERTIES to ne
documented in one place...!
Dag
On 26/07/13 05:51, Rick Hillegas wrote:
On 7/25/13 11:39 AM, Rick Hillegas wrote:
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
Here are another two --derby-properties overrides:
FromList.verifyProperties():
joinOrder
useStatistics
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
