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 |
