SQL/MX Programming Manual for Java

SQL/MX Programming Considerations
HP NonStop SQL/MX Programming Manual for Java—523726-003
4-39
UPDATE STATISTICS Statement
UPDATE STATISTICS Statement
The UPDATE STATISTICS statement updates the histogram statistics for one or more
groups of columns within an SQL/MP or SQL/MX table to enable optimized access
plans. For more information about the UPDATE STATISTICS statement, see the
SQL/MX Reference Manual.
Follow these guidelines when embedding an UPDATE STATISTICS statement in an
SQLJ program.
ANSI Compliance and Portability
If program portability is important, note that the UPDATE STATISTICS statement is an
SQL/MX extension to the ANSI standard.
Placement of the UPDATE STATISTICS Statement
Place the UPDATE STATISTICS statement anywhere an executable statement is
allowed in an SQLJ program. You cannot place this statement at the top-level scope of
a program where you code class declarations. If you associate a connection context
with the UPDATE STATISTICS statement, place the UPDATE STATISTICS statement
after the instantiation of the connection context object.
Scope of the UPDATE STATISTICS Statement
An UPDATE STATISTICS statement in an SQLJ program always updates the
histogram statistics at run time, not compile time. Because SQL/MX generates access
plans for DML statements at compile time, statically compiled DML statements in the
same SQLJ program as an UPDATE STATISTICS statement do not use the updated
histogram statistics in their access plans.
For example, this statically compiled SELECT statement does not use the updated
histogram statistics generated by the UPDATE STATISTICS statement in the same
program:
#sql {UPDATE STATISTICS FOR TABLE samdbcat.persnl.employee
ON (jobcode),(empnum, deptnum)
GENERATE 10 INTERVALS};
#sql {SELECT last_name
INTO :empname
FROM samdbcat.persnl.employee
WHERE empnum = 65
};
Dynamically run DML statements in the same SQLJ program as an UPDATE
STATISTICS statement use the updated histogram statistics as long as the DML
statements follow the UPDATE STATISTICS statement in execution order. For more
information about the causes of dynamic execution, see Causes of Dynamic Execution
on page 4-2.