ALLBASE/SQL Reference Manual (36216-90216)

Chapter 12 531
SQL Statements S - Z
SETOPT
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.