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 <[email protected]>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 >>> >>> >> >> >
