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
|
