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 mike matrigali <mi...@gmail.com> on 2013/07/25 17:41:54 UTC
is there an optimizer override to force multi-probe query plan?
Is there an optimizer override in 10.8 to force a multi-probe query plan?
If not, is there an override in the new work being done in trunk to
force it?
Also if not, is there any suggestion of what the right syntax would be
to add it to the old style optimizer override?
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
Re: is there an optimizer override to force multi-probe query plan?
Posted by Mamta Satoor <ms...@gmail.com>.
Hi,
When optimizer overrides was originally added, following overrides were
supported
1) constraint – The Derby optimizer chooses an index, including the indexes
that enforce constraints, as the access path for query execution if it is
useful. If there is more than one useful index, in most cases, Derby
chooses the index that is most useful. User can override the optimizer’s
selection and force use of a particular index or force a table scan. To
force use of the index that enforces a primary key or unique constraint,
use this property, specifying the unqualified name of the constraint.
2) index – This is similar to constraint property. To force use of a
particular index, specify the unqualified index name. To force a table
scan, specify null for the index name.
3) joinOrder – Will allow users to override the optimizer’s choice of join
order for two tables. When the value FIXED will be used, the optimizer will
choose the order of tables as they appear in the FROM clause as the join
order.
4) joinStrategy – This property will allow users to override the
optimizer’s choice of join strategy. The 2 types of join strategy are
called nested loop and hash. In a nested loop join strategy, for each
qualifying row in the outer table, Derby uses the appropriate access path
(index or table scan) to find the matching rows in the inner table. In a
hash join strategy, Derby constructs a hash table representing the inner
table. For each qualifying row in the outer table, Derby does a quick
lookup on the hash table to find the matching rows in the inner table.
Derby has to scan the inner table or index only once to create the hash
table. The --PROPERTIES must appear directly after the inner table. Use
this optimizer override with the joinOrder property only. Do not let the
optimizer choose the join order.
With the exception of joinStrategy (which is allowed on all table
expressions), constraint and index can be specified only on base tables;
they are not allowed on views or derived tables.
Rick mentioned few additional overrides which probably were added later. If
we already have optimizer override for useStatistics, then should we close
DERBY-4113(Make new optimizer directive for USESTATISTICS=<true|false>).
There was no override added for multi-probe in the original work.
thanks,
Mamta
On Thu, Jul 25, 2013 at 12:51 PM, Rick Hillegas <ri...@oracle.com>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
>>>
>>>
>>
>>
>
Re: is there an optimizer override to force multi-probe query plan?
Posted by Rick Hillegas <ri...@oracle.com>.
On 7/27/13 3:45 PM, Dag H. Wanvik wrote:
> ...
>
> 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...!
+1. https://issues.apache.org/jira/browse/DERBY-6305 may be a place to
work through this.
Thanks,
-Rick
>
> Dag
>
>
> ...
Re: is there an optimizer override to force multi-probe query plan?
Posted by "Dag H. Wanvik" <da...@oracle.com>.
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
>>>
>>
>>
>
Re: is there an optimizer override to force multi-probe query plan?
Posted by Rick Hillegas <ri...@oracle.com>.
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
>>
>
>
Re: is there an optimizer override to force multi-probe query plan?
Posted by Rick Hillegas <ri...@oracle.com>.
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
>