1.0

Chapter 46
Overriding Optimizer Choices
You can override the default behavior of the SQLFire query optimizer by including a -- SQLFIRE-PROPERTIES
clause and a property definition in a SQL statement. The clause and property definition both appear within the context
of a SQL comment (beginning with two dashes "--").
Because optimizer properties are expressed as comments, they must appear at the end of a line. If you want to continue
the SQL statement after including a property definition, enter a newline (\n) character before continuing the statement.
SQLFire supports two general categories of properties:
• FROM clause properties—These property definitions must be specified between a query's FROM clause and the first
table name:
FROM [ -- SQLFIRE-PROPERTIES fromProperty = value \n ]
TableExpression [, TableExpression ]*
• Table properties—These properties apply to the preceding base table, and the property definition must appear at the
end of the TableExpression (immediately after the base table name or alias, and before any addition table name,
JOIN clause, or comma):
{ table-name | view-name }
[ [ AS ] correlation-Name
[ ( simple-column-name [ , simple-column-name ]* ) ] ]
[ -- SQLFIRE-PROPERTIES tableProperty = value ]
The space between -- and SQLFIRE-PROPERTIES is optional.
Note: Make sure that you adhere to the correct syntax when using the -- SQLFIRE-PROPERTIES clause.
Failure to do so can cause the parser to interpret it as a comment and ignore it.
Note: SQLFire also provides optimizer properties that are only used when querying the MEMORYANALYTICS
system table on page 609. See Estimating Memory Requirements on page 63.
The following FROM clause and table properties are supported.
DescriptionTypeProperty Name
To force the use of the index that enforces a
primary key, a foreign key, or unique constraint,
Table propertyconstraint
use the constraint property and specify the
unqualified name of the constraint. The
constraint property can be used only within a
TableExpression, and it can be specified only
265