SETOPT

The SETOPT statement modifies the access optimization plan used by queries.

Scope

ISQL or Application Programs

Syntax — SETOPT

  SETOPT {CLEAR 
          GENERAL {ScanAccess 
                   JoinAlgorithm}[,...]
          BEGIN {GENERAL {ScanAccess 
                          JoinAlgorithm}}[,...] END

Syntax — Scan Access

  [NO]{SERIALSCAN 
       INDEXSCAN 
       HASHSCAN 
       SORTINDEX}

Syntax — Join Algorithm

  [NO] {NESTEDLOOP 
        NLJ 
        SORTMERGE 
        SMJ}

Parameters

CLEAR: specifies that the access plan set by any previous SETOPT statement is to be cleared.
SERIALSCAN: specifies serial scan access.
INDEXSCAN: indicates index scan access for those tables with indexes.
HASHSCAN: designates hash scan access for tables with hash structures.
SORTINDEX: indicates index scan access when an ORDER BY or GROUP BY clause is specified in a SELECT statement. Therefore, the extra sort operation is eliminated. The index scanned is the one defined upon the column referenced in the ORDER BY or GROUP BY clause.
NESTEDLOOP: specifies nested loop joins.
NLJ: is equivalent to NESTEDLOOP.
SORTMERGE: designates sort merge join.
SMJ: is equivalent to SORTMERGE.

Description

Use the SETOPT statement when you want to override the default access plan used in queries.
The SETOPT statement affects only those queries in the current transaction. When the transaction ends, the settings specified by SETOPT are cleared.
To view the plan specified by SETOPT, query the SYSTEM.SETOPTINFO view.
Use the GENPLAN command in ISQL to display the current access plan.
NLJ is equivalent to NESTEDLOOP, and SMJ is equivalent to SORTMERGE.
To store a user defined access plan in a module or procedure, run ISQL and issue the SETOPT statement followed by a VALIDATE statement.
To remove the access plan specified by a SETOPT statement from a module or procedure, execute the VALIDATE statement with the DROP SETOPTINFO option.
When using the EXTRACT command in ISQL, specify the NO SETOPTINFO option if you want to prevent the access plan specified by a SETOPT statement from being included in the installable module file.
Use the GENPLAN command in ISQL to see the optimizer's access plan for an ALLBASE/SQL statement.
For more information on joins, see "Join Methods" in the ALLBASE/SQL Performance and Monitering Guidelines.

Authorization

You do not need authorization to use the SETOPT statement.

Examples

In the following example, the SETOPT statement specifies that all tables with indexes are accessed with an index scan. Since PurchDB.Parts has an index defined upon the PartNumber column, an index scan is executed by the first SELECT statement. The effect of a SETOPT statement lasts only until the end of the transaction. Therefore, the second SELECT statement may, or may not, use an index scan.

   BEGIN WORK
   SETOPT GENERAL INDEXSCAN
   SELECT * FROM PurchDB.Parts
   COMMIT WORK
 
   BEGIN WORK
   SELECT * FROM PurchDB.Parts
   COMMIT WORK

The next SETOPT statement indicates that hash scans are not to be performed.

SETOPT GENERAL NO HASHSCAN

The following two SETOPT statements are equivalent.

   SETOPT GENERAL HASHSCAN, NO SORTMERGE
 
   SETOPT BEGIN
          GENERAL HASHSCAN;
          GENERAL NO SORTMERGE;
          END

In the following two SELECT statements, an index scan is performed upon the PartNumber because the PartNumber column is referenced in the ORDER BY and GROUP BY clauses.

   SETOPT GENERAL SORTINDEX
 
   SELECT   PartNumber, UnitPrice
   FROM     PurchDB.SupplyPrice
   ORDER BY PartNumber, UnitPrice
 
   SELECT   PartNumber, AVG (UnitPrice)
   FROM     PurchDB.SupplyPrice
   GROUP BY PartNumber

After the following sequence of statements is executed, all of the modules stored in the DBEnvironment will use an index scan when accessing tables with indexes. The cex09 module is an exception, however, because it is validated with the DROP SETOPTINFO keywords. When the cex03 module is copied into the installable module file with the EXTRACT command, the index scan specified by the SETOPT statement is not included in the installable module file.

   SETOPT GENERAL INDEXSCAN
   VALIDATE ALL MODULES
   SETOPT CLEAR
   VALIDATE DROP SETOPTINFO MODULE cex09
   EXTRACT MODULE cex03 NO SETOPTINFO INTO Modfile