HP 3000 Manuals HP 3000 Manuals
SETOPT
The SETOPT statement modifies the access optimization plan used by
queries.
Scope
ISQL or Application Programs
Syntax--SETOPT
{CLEAR }
{GENERAL {ScanAccess } [,...] }
SETOPT { {JoinAlgorithm} }
{BEGIN {GENERAL {ScanAccess }} [;...] END}
{ { {JoinAlgorithm}} }
Syntax--Scan Access
{SERIALSCAN}
[NO] {INDEXSCAN }
{HASHSCAN }
{SORTINDEX }
Syntax--Join Algorithm
{NESTEDLOOP}
[NO] {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 Monitoring 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