The GENPLAN statement can be useful in determining the way to
write a SELECT, UPDATE, or DELETE statement for maximum performance.
By issuing the GENPLAN statement in ISQL, you can see the optimizer's
access plan for a given statement.
Suppose you have written an application containing a query, as in the following example:
SELECT PartName, VendorNumber, UnitPrice
INTO :PartName, :VendorNumber, :UnitPrice
FROM PurchDB.Parts p, PurchDB.SupplyPrice sp
WHERE p.PartNumber = sp.PartNumber
AND p.PartNumber = :PartNumber
|
You run the application and want to improve its performance.
One approach would be to issue the ISQL GENPLAN statement with parameters
for the embedded query. This provides any scan types and join types for a given statement by query block. You can change the statement in your application, run the application to check any change in performance, and, if necessary, again use GENPLAN to determine the specific access path.
To convert a statement to GENPLAN format:
Remove the SELECT statement INTO clause.
Remove any null indicator variables from the select statement.
In the GENPLAN statement WITH clause, define any input host variables found in the SELECT statement WHERE clause. You must ensure that the SQL data type specified for each variable in the WITH clause is compatible with the data type declared in the application for the host variable. Refer to ALLBASE/SQL Reference Manual, SQL Commands chapter, for data type compatibility charts for each supported language.
Your GENPLAN statement for the above query would be as follows (The
input host variable is shaded.):
GENPLAN WITH (PartNumber char(16)) FOR
SELECT PartName, VendorNumber, UnitPrice
FROM PurchDB.Parts p, PurchDB.SupplyPrice sp
WHERE p.PartNumber = sp.PartNumber
AND p.PartNumber = :PartNumber
|
To display the access plan generated by GENPLAN, issue the following statement within the same transaction as the GENPLAN statement:
SELECT * FROM System.Plan
|
The GENPLAN statement can only be used in ISQL. It cannot be used in an application in a static SQL statement nor in dynamic preprocessing.
Refer to the ALLBASE/SQL Reference Manual for detailed syntax and information regarding scans and indexes.
