# Overriding the default optimizer behavior

You can override the default behavior of the Derby query optimizer by including a `--DERBY-PROPERTIES` clause and an associated property as a comment within an SQL statement.

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.

Important: 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, Derby uses the appropriate access path (index or table scan) to find the matching rows in the inner table. In a hash join strategy, Derby constructs a hash table that represents the inner table. For each qualifying row in the outer table, Derby does a quick lookup on the hash table to find the matching rows in the inner table. Derby 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

Parent topic: DML statements and performance
Related concepts
Performance and optimization
Locking and performance
Non-cost-based optimizations