db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Eric Radzinski <ericr...@yahoo.com>
Subject documenting DERBY -573, optimizer overrides
Date Fri, 20 Jan 2006 23:22:53 GMT
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 it 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 force 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 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 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.


		
---------------------------------
 
 What are the most popular cars?  Find out at Yahoo! Autos
Mime
View raw message