Because optimizer overrides are expressed as comments, they must be included
at the end of a line. You can specify optimizer override properties for an
entire FROM clause, for tables in the FROM clause, or for both.
The syntax for a FROM clause property is as follows:
FROM [ -- DERBY-PROPERTIES joinOrder = { FIXED | UNFIXED } ]
tableExpression [,tableExpression]*
The syntax for table optimizer override properties, which must be included
at the end of a tableExpression, is as follows:
{tableName | viewName }
[ [ AS ] correlationName
[ (simpleColumnName [ , simpleColumnName ]* ) ] ]
[ -- DERBY-PROPERTIES { constraint = constraintName | index = indexName | joinStrategy = { NESTEDLOOP | HASH } } ]
The space between -- and DERBY-PROPERTIES
is optional.
Make sure that you adhere to the correct syntax when
using the --DERBY-PROPERTIES clause. Failure to do so can
cause the parser to interpret it as a comment and ignore it. To verify that the
parser interpreted your overrides correctly, you can use RunTimeStatistics. See
Optimizer overrides for more
information.
The following four properties are available for use in a
--DERBY-PROPERTIES clause:
- constraint
- 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
- 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
- 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
- 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,
uses the appropriate access path (index or table scan) to find the matching
rows in the inner table. In a hash join strategy,
constructs a hash table
that represents the inner table. For each qualifying row in the outer table,
does a quick lookup on
the hash table to find the matching rows in the inner table.
needs to scan the inner
table or index only once to create the hash table. The
--DERBY-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.
The following examples illustrate the use of the
--DERBY-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 --DERBY-PROPERTIES constraint=cons1
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 --DERBY-PROPERTIES index=t1_c1
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 --DERBY-PROPERTIES joinOrder=FIXED
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 --DERBY-PROPERTIES joinOrder=FIXED
t1 a, t1 b --DERBY-PROPERTIES joinStrategy=NESTEDLOOP
WHERE a.c1=b.c1