Overriding Optimizer Choices

You can override the default behavior of the GemFire XD query optimizer by including a -- GEMFIREXD-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, GemFire XD 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.
GemFire XD 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 [ -- GEMFIREXD-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 ]* ) ] ]
             [ -- GEMFIREXD-PROPERTIES tableProperty = value ]

The space between -- and GEMFIREXD-PROPERTIES is optional.

Note: Make sure that you adhere to the correct syntax when using the -- GEMFIREXD-PROPERTIES clause. Failure to do so can cause the parser to interpret it as a comment and ignore it.
Note: GemFire XD also provides optimizer properties that are only used when querying the MEMORYANALYTICS. See Managing Tables in Memory.

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, GemFire XD uses the appropriate access path (index or table scan) to find the matching rows in the inner table.
Note: As a best practice, create indexes on all columns that are used to join tables.
In a hash join strategy, GemFire XD constructs a hash table that represents the inner table. For each qualifying row in the outer table, GemFire XD does a quick lookup on the hash table to find the matching rows in the inner table. GemFire XD needs to scan the inner table or index only once to create the hash table. The -- GEMFIREXD-PROPERTIES parameter must immediately follow the inner table.
Note: Joins that return a large number of rows can fail with an OVERFLOW_TO_DISK_UNSUPPORTED exception if GemFire XD does not have enough runtime memory to build the hash table. If you receive this error, check the joined columns and create indexes as necessary, or specify a different join strategy.

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 suboptimal 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), GemFire XD displays the alias name instead of the original, system-generated statement alias. See Using VSD to Analyze Statistics.
Note: You cannot include whitespace characters as word separators in the alias name.
withMemoryFootprint Table property Use this option when querying the sys.memoryanalytics table to obtain a more accurate footprint of memory usage for your tables.
Note: Use sizerHints=withMemoryFootPrint with caution because it uses the Java agent and requires lots of memory to process. It should be applied on to very small dataset (typically couple of hundred rows) and should never be used in a production system. See Using sizerHints When Querying sys.memoryanalytics.
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, GemFire XD 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 -- GEMFIREXD-PROPERTIES clause. Note that these examples show the newline character as \n to emphasize that any part of the SQL statement that continues beyond the optimizer hint must begin on a new line:

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 -- GEMFIREXD-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 -- GEMFIREXD-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 -- GEMFIREXD-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 -- GEMFIREXD-PROPERTIES joinOrder=FIXED \n 
t1 a, t1 b -- GEMFIREXD-PROPERTIES joinStrategy=NESTEDLOOP \n 
WHERE a.c1=b.c1;

statementAlias

With the following query, GemFire XD uses that statement alias "My_QUERY" instead of a system-generated alias:
SELECT * FROM -- GEMFIREXD-PROPERTIES statementAlias=My_QUERY \n 
userTable t1 -- GEMFIREXD-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 -- GEMFIREXD-PROPERTIES withSecondaries=true \n 
GROUP BY dsid();

withMemoryFootPrint

This query displays the complete memory footprint of tables and indexes:
SELECT * FROM sys.memoryAnalytics -- GEMFIREXD-PROPERTIES sizerHints=withMemoryFootPrint \n
;

You must also start GemFire XD members with the Java agent in order to use this optimizer hint. See Using sizerHints When Querying sys.memoryanalytics.