SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
S-78
Statistics
Statistics
SQL has an UPDATE STATISTICS statement you can use to collect and save statistics
on columns and tables. The SQL compiler uses these statistics to determine the
selectivity of predicates, indexes, and tables. Because selectivity directly influences the
cost of access plans and regular collection of statistics, it increases the likelihood that
SQL will choose efficient access plans.
A NonStop SQL/MP installation should follow these rules for updating statistics:

Do not use the UPDATE STATISTICS statement until you use FILEINFO with the
STATISTICS option to see if performance problems are caused by fragmentation.
By using FILEINFO, you should be able to determine if the performance is being
impeded by fragmentation of blocks in a table. In that case, running UPDATE
STATISTICS and recompiling the queries does not help. You should first reload the
table online, by using the FUP RELOAD command.

Determine the effect of UPDATE STATISTICS on production queries first. In an
SQLCI session, issue a BEGIN WORK command and then issue UPDATE
STATISTICS. Use EXPLAIN to see if the new statistics would give you the correct
query plan. The UPDATE STATISTICS and EXPLAIN commands should be issued
within a transaction so that the UPDATE STATISTICS operation can be backed out
easily if necessary.

Specify the NORECOMPILE option in the UPDATE STATISTICS statement so that
dependent programs are not invalidated. By default, an UPDATE STATISTICS
operation invalidates dependent programs. Even if UPDATE STATISTICS is
executed within a transaction that can be backed out, if the NORECOMPILE option
is omitted, dependent programs are still invalidated because program file labels
are not audited. Updates to program file labels are not backed out.
Storage Management Foundation (SMF)
The SMF subsystem is an optional HP product. SMF simplifies name, storage, and file
management with logical files, virtual volumes, and storage pools. On any SMF node,
logical files, virtual volumes, and storage pools can coexist with conventional Guardian
files and volumes. In the context of SMF, conventional Guardian files and volumes are
called direct files and direct volumes.
With SMF, SQL objects can be either direct or logical files. A logical file is a file that
resides on a virtual volume; a virtual volume is actually a process that uses a pool of
physical volumes for storage. When you create an SQL object on a virtual volume, the
object is a logical file, and you always refer to it by logical name. The object can be
located on any physical volume in the storage pool for the virtual volume you specify;
SMF automatically determines the best location.