Optimizer Overrides
Page 1 of 6

6
Optimizer Overrides

Cloudscape's query optimizer usually makes the best choice of join order and access path. In addition, the default values for join strategy and row fetch are usually best. However, there are some cases in which you may want to override the optimizer or the default values. Optimizer overrides allow users to hand-tune the optimizer for queries and updates and deletes with WHERE clauses.

Optimizer overrides are specified within a PROPERTIES clause within the SQL-J statement.

A PROPERTIES clause has the following syntax:

PROPERTIES propertyName = value [, propertyName = value]*

A propertyName is case-sensitive.

An exception will be thrown if there are semantic errors in the PROPERTIES clause such as:

  • invalid properties
  • nonexistent property values, such as nonexistent access paths
  • invalid property values, such as invalid access paths

Optimizer-override properties belong to one of the following scopes:

  • an entire FROM clause
  • a particular table in the FROM clause

FROM clause optimizer-override PROPERTIES clauses, which belong to an entire FROM clause, come immediately after the word FROM, like this:

FROM [ PROPERTIES joinOrder = { FIXED | UNFIXED } ]
    TableExpression [,TableExpression]*

Table optimizer-override PROPERTIES clauses, which belong to a particular table in the FROM clause, come at the end of the TableExpression, like this:

{ TableName | ViewName }
    [ [ AS ] CorrelationName
        [ (SimpleColumnName [ , SimpleColumnName]* ) ] ] ]
[ PROPERTIES clause ]

For more information about a TableExpression, see TableExpression in the Cloudscape Reference Manual.

With the exception of joinStrategy (which is allowed on all table expressions), such properties can be specified only on base tables; they are not allowed on views or derived tables. They are allowed on any base table within a FROM clause or any table within a FROM clause in a subquery. They can be used on the base tables specified in a CREATE VIEW statement, within a derived table (subquery in the FROM list), and within a JOIN clause.

Errors for table optimizer override PROPERTIES clauses in a CREATE VIEW statement are not returned until the view is first used in a DML statement.

Optimizer-override properties include: