1.0

DescriptionTypeProperty Name
Note: You cannot include whitespace
characters as word separators in the
alias name.
Use this property to include secondary buckets
for a partitioned table when joining the table
Table propertywithSecondaries
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
267
Overriding Optimizer Choices