Here's my attempt at documenting the optimizer
overrides that are introduced by DERBY -573. I'm assuming that this
information would be added to the Tuning Guide, probably as a topic
within "Performance and optimization" (let me know if anything needs to
be added to the Ref Guide or any other book(s)).
Overriding the default optimizer behavior
You can override the default behavior of the Derby query
optimizer by including a --DERBY PROPERTIES clause and an associated
property as a comment within an SQL statement.
Because optimizer overrides are expressed as comments, they must
be included at the end of a line. You can specify optimizer override
properties for an entire FROM clause, for tables in the FROM clause, or
for both.
The syntax for FROM clause properties is:
FROM [ -- DERBY-PROPERTIES propertyName
= value ]
TableExpression [,TableExpression]*
The syntax for table optimizer override properties, which must
be included at the end of a TableExpression, is:
{TableName | ViewName }
[ [ AS ] CorrelationName
[ (SimpleColumnName [ , SimpleColumnName]* ) ] ]
[ -- DERBY-PROPERTIES clause]
Note that the space between -- and
DERBY-PROPERTIES is optional.
Important: Make sure that you
adhere to the correct syntax when using the -- DERBY PROPERTIES clause.
Failure to do so will cause the parser to interpret i t as a comment
and ignore it.
The following four properties are
available for use in a --DERBY PROPERTIES clause:
constraint
The Derby optimizer chooses an index,
including the indexes that enforce constraints, as the access path for
query execution if the index is useful. If there is more than one
useful index, in most cases Derby chooses the index that is most
useful. Use the constraint property to override the index that the
optimizer selects and force the use of a particular index or force a
table scan. To force the use of the index that enforces a primary key
or unique constraint, use the constraint property and specify the
unqualified name of the constraint. The constraint property can be
specified only on base tables; it cannot be specified on views or
derived tables.
index
The index property is similar to the
constraint property. To forc e use of a particular index, specify the
unqualified index name. To force a table scan, specify null for the
index name. The index property can be specified only on base tables; it
cannot be specified on views or derived tables.
joinOrder
Use the joinOrder property to
override the optimizer’s choice of join order for two tables. When the
value FIXED is specified, the optimizer will choose the order of tables
as they appear in the FROM clause as the join order. Valid values for
the joinOrder property include FIXED and UNFIXED.
The joinOrder property can be
specified on all table expressions.
joinStrategy
Use the joinStrategy property to
override the optimizer’s choice of join strategy. The two 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 app ropriate 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 that represents 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 needs to
scan the inner table or index only once to create the hash table. The
–DERBY-PROPERTIES parameter must immediately follow the inner table.
The joinOrder property can be specified on all table expressions. Valid
values include HASH and NESTED LOOP.
The joinStrategy property can be
specified on all table expressions, but it must be used in conjunction
with the joinOrder property. Do not let the optimizer choose the join
order.
Examples:
I could use some help with some examples here.
