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. See Estimating Memory Requirements.

The following FROM clause and table properties are supported.

Property Name Type Description
constraint Table property To force the use of the index that enforces a primary key, a foreign key, or unique constraint, 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 on base tables; it cannot be specified on views or derived tables.
index Table property 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 used only within a TableExpression, and it can be specified only on base tables; it cannot be specified on views or derived tables.
joinOrder FROM clause property 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 used with a FROM clause.
joinStrategy Table property

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, SQLFire uses the appropriate access path (index or table scan) to find the matching rows in the inner table. In a hash join strategy, SQLFire constructs a hash table that represents the inner table. For each qualifying row in the outer table, SQLFire does a quick lookup on the hash table to find the matching rows in the inner table. SQLFire needs to scan the inner table or index only once to create the hash table. The -- SQLFIRE-PROPERTIES parameter must immediately follow the inner table.

Typically, you will use the joinStrategy property only in conjunction with the joinOrder property. Specifying a join strategy without knowing the join order can result in less-than-optimal performance.

Valid values include HASH and NESTEDLOOP. The joinStrategy property can be used only within a TableExpression.

statementAlias FROM clause property Use this property to create an alias for a user query. When using the Visual Statistics Display (VSD), SQLFire displays the alias name instead of the original, system-generated statement alias.
Note: You cannot include whitespace characters as word separators in the alias name.
withSecondaries Table property Use this property to include secondary buckets for a partitioned table when joining the table with a virtual table. When you set this property to TRUE, SQLFire considers secondary buckets for routing and scanning purposes when joining the partitioned table with a virtual table. When set to FALSE, secondary buckets are never considered in the query.
Note: This table property can only be used with base tables or virtual tables.

Examples

The following examples illustrate the use of the -- SQLFIRE-PROPERTIES clause:

constraint

CREATE TABLE t1 (c1 int, c2 int, c3 int, CONSTRAINT cons1 PRIMARY KEY (c1, c2));
INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
SELECT * FROM t1 -- SQLFIRE-PROPERTIES constraint=cons1 \n FOR UPDATE;

index

CREATE TABLE t1 (c1 int, c2 int, c3 int, CONSTRAINT cons1 PRIMARY KEY (c1, c2));
INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
CREATE INDEX t1_c1 ON t1(c1);
SELECT * FROM t1 -- SQLFIRE-PROPERTIES index=t1_c1 \n WHERE c1=1

joinOrder

CREATE TABLE t1 (c1 int, c2 int, c3 int, CONSTRAINT cons1 PRIMARY KEY (c1, c2));
CREATE TABLE t2 (c1 int not null, c2 int not null, c3 int, CONSTRAINT cons2 UNIQUE (c1, c2));
INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
INSERT INTO t2 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
SELECT * FROM -- SQLFIRE-PROPERTIES joinOrder=FIXED \n t1, t2 WHERE t1.c1=t2.c1;

joinStrategy

CREATE TABLE t1 (c1 int, c2 int, c3 int, CONSTRAINT cons1 PRIMARY KEY (c1, c2));
CREATE TABLE t2 (c1 int not null, c2 int not null, c3 int, CONSTRAINT cons2 UNIQUE (c1, c2));
INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
INSERT INTO t2 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
SELECT * FROM -- SQLFIRE-PROPERTIES joinOrder=FIXED \n t1 a, 
t1 b -- SQLFIRE-PROPERTIES joinStrategy=NESTEDLOOP \n WHERE a.c1=b.c1;

statementAlias

With the following query, SQLFire uses that statement alias "My_QUERY" instead of a system-generated alias:
SELECT * FROM -- SQLFIRE-PROPERTIES statementAlias=My_QUERY \n 
userTable t1 -- SQLFIRE-PROPERTIES index=IDX_COL1 \n WHERE t1.col1 is not null

withSecondaries

This query considers secondary buckets when joining the partitioned table with sys.members:
SELECT dsid(), count(1) from sys.members , userPartitionedTable t1
-- SQLFIRE-PROPERTIES withSecondaries=true \n GROUP BY dsid();