ALLBASE/SQL Reference Manual (36216-90216)

532 Chapter12
SQL Statements S - Z
SETOPT
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