1.1.1

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 character (\n) before continuing the statement.
This rule applies to the terminating semicolon, if the optimizer hint appears at the end of the SQL statement.
Note: When processing a SQL script file, SQLFire does not recognize other parts of a SQL statement if they
appear at the end of a SQL comment line. For this reason, you must place any remaining parts of the SQL
statement on a separate line. This includes the terminating semicolon, if an optimizer hint extends to the end of
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
on page 661. See Estimating Memory Requirements on page 73.
The following FROM clause and table properties are supported.
279