HP 3000 Manuals HP 3000 Manuals
Chapter 8 Analyzing Queries with GENPLAN
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.