db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik" <dag.wan...@oracle.com>
Subject Re: is there an optimizer override to force multi-probe query plan?
Date Sat, 27 Jul 2013 22:45:06 GMT
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
>>>
>>
>>
>


Mime
View raw message