1.1

ManualsBrandsVMware ManualsApplicationsvFabric SQLFire

Table Of Contents

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

unqualiﬁed name of the constraint. The

constraint property can be used only within a

TableExpression, and it can be speciﬁed only

on base tables; it cannot be speciﬁed on views

or derived tables.

The index property is similar to the constraint

property. To force use of a particular index,

Table propertyindex

specify the unqualiﬁed 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 speciﬁed only

on base tables; it cannot be speciﬁed on views

or derived tables.

Use the joinOrder property to override the

optimizer's choice of join order for two tables.

FROM clause propertyjoinOrder

When the value FIXED is speciﬁed, 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.

Use the joinStrategy property to override the

optimizer's choice of join strategy. The two

Table propertyjoinStrategy

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 ﬁnd 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 ﬁnd 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.

vFabric SQLFire User's Guide276

Managing and Monitoring vFabric SQLFire