Optimizer overrides - functional spec

[Mamta Satoor]

Hi,

 

I am looking at implementing optimizer overrides in Derby for 10.2 release. Derby engine in fact already supports some optimizer overrides but they are not user visible. I have attached the functional spec for more information. Let me know if anyone has any comments.

 

thanks,

Mamta

Title: Optimizer Overrides

Optimizer Overrides Contents Overview Standards Functionality Design Overview Availability under Network Server Documentation Miscellaneous Overview Derby engine supports some optimizer overrides but they are not exposed to the users. Over the past year, there have been few postings on Derby mailing list looking for ways to provide optimizer hints. This functional spec covers exposing those optimizer hints in a manner that will not break when ported to a different database. Standards SQL standard doesn’t specify a way of providing optimizer overrides and hence the proposal here is to include these overrides in SQL comments. That implementation choice will make sure that when the SQL with optimizer override comments is ported to some other database, it will not break. Chances are that the optimizer overrides provided through SQL comments will be ignored by other SQL engines. ORACLE supports optimizer hints through SQL comments. Sybase has WITH table-hint option in the FROM clause of the SQL statement to support optimization hints. Functionality Derby users will be able to provide optimizer overrides through comments --PROPERTIES within the SQL statement. The available overrides would be constraint, index, joinOrder and joinStrategy. The property names will be case-sensitive and the syntax would be as follows --PROPERTIES propertyName = value [, propertyName = value]* Notice, there is no space between -- and PROPERTIES. Optimizer override properties can be specified for an entire FROM clause and/or for tables in the FROM clause. Syntax for FROM clause properties    FROM [ --PROPERTIES joinOrder = { FIXED | UNFIXED}]         TableExpression [,TableExpression]* Syntax for table optimizer override properties (this come at the end of TableExpression, like following)    {TableName | ViewName }         [ [ AS ] CorrelationName           [ (SimpleColumnName [ , SimpleColumnName]* ) ] ]         [--PROPERTIES clause] Following will be the supported properties 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. Design Overview The main hurdle in implementing this feature is to have parser distinguish between a user comment starting with -- and optimizer overrides starting with --PROPERTIES. The properties mentioned above are already recognized by Derby but USER has no way to use them in Derby 10.1 (these overrides are available internally to Derby engine code and is used by metadata sql, import utility etc). Once the parser is changed to recognize --PROPERTIES as optimizer overrides, existing metadata sqls, import utility etc will need to be changed to use this new way of specifying the optimizer overrides. And as with any new functionality, tests will need to be added to test these optimizer overrides (which will be run in both embedded and Network Server). Availability under Network Server These optimizer overrides should work under Network Server too since the sql will be passed by the client to the server (along with all the comments). Documentation These overrides will need to be documented. Miscellaneous If the user will not follow --PROPERTIES syntax for specifying the optimizer overrides, parser will simply treat them as comments and ignore them. So, be careful with the syntax, don’t leave any blanks between -- and PROPERTIES. The parser can always be modified to be more flexible about this syntax but this is what I am shooting for at this point.