About this task
The cost you specify for a function must be compatible
with the cost that the optimizer calculates for other parts of the
SQL statement. The following formula is one method to approximate
the costing algorithm that the optimizer uses:
Procedure
- Execute the following SQL statements from DB-Access, where
bigtable is
any large table:SET EXPLAIN ON;
SELECT count(*) from bigtable;
Time the query.
- Let secost be the cost the optimizer assigned for
the scan.
Read the
sqexplain.out file
to get
secost.
For information about sqexplain.out,
refer to the HCL OneDB™ Performance
Guide.
- Let satime be the time required to complete the
SQL statement.
- Execute and time your function.
Let
facost be
the actual time required to execute the function once.
The cost
of executing the function once can be approximated as follows:
((secost/satime)*facost)
Truncate
the calculated cost to an integer value.