ALLBASE/SQL Reference Manual
> Chapter 12 SQL Statements S - ZSETOPT |
||||||||||||||||||||||||
|
ScopeISQL or Application Programs Syntax — SETOPTSETOPT {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
Description
AuthorizationYou do not need authorization to use the SETOPT statement. ExamplesIn 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 WORKThe next SETOPT statement indicates that hash scans are not to be performed. SETOPT GENERAL NO HASHSCANThe following two SETOPT statements are equivalent. SETOPT GENERAL HASHSCAN, NO SORTMERGE SETOPT BEGIN GENERAL HASHSCAN; GENERAL NO SORTMERGE; ENDIn 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 PartNumberAfter 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
|