NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
U-8
UPDATE STATISTICS Statement
RECOMPILE | NO RECOMPILE
specifies whether to invalidate program files that use the table affected by the
UPDATE STATISTICS operation:
RECOMPILE is the default behavior. SQL uses the RECOMPILE option if you do
not specify either option.
Only recompiled programs can take advantage of the new statistics. Invalidating
programs forces recompilation unless the programs were compiled with the CHECK
INOPERABLE PLANS option. SQL automatically recompiles an invalid program
at execution time (unless you specified options to restrict recompilation when you
originally compiled it), but you need to explicitly recompile each program to
revalidate it.
If you specify RECOMPILE, you might want to use VERIFY to display the names
of invalid programs. See VERIFY Command
on page V-2.
SIMPLE | PROBABILISTIC
specifies whether to compute statistics using a new internal algorithm, called
PROBABILISTIC, or the algorithm used in previous releases of NonStop SQL/MP,
called SIMPLE.
The PROBABILISTIC algorithm is designed to give more accurate results than the
previous (SIMPLE) algorithm. Moreover, when you specify the PROBABILISTIC
option, SQL computes statistics in parallel on partitioned tables; SQL operates in
parallel on each partition in a table.
If you specify the PROBABILISTIC option, SQL ignores the EXACT and
SAMPLE n BLOCKS options. With the PROBABILISTIC algorithm, SQL always
reads every row in the table. If you specify the SIMPLE option, SQL uses either the
EXACT or the SAMPLE n BLOCKS option, depending on which you specify.
The default option is the SIMPLE algorithm.
In future releases of NonStop SQL/MP, SQL might switch to using the
PROBABILISTIC option as the default option. At that time, an UPDATE
STATISTICS statement that has no options specified would no longer use the
SIMPLE algorithm, but instead use the PROBABILISTIC algorithm. If you want to
continue using the SIMPLE algorithm in future releases of NonStop SQL/MP, it is
recommended that you explicitly specify the SIMPLE option in your UPDATE
STATISTICS statements in embedded SQL or OBEY files.
EXACT | SAMPLE n BLOCKS
specifies whether to compute statistics by reading each row in each partition of the
table (EXACT) or to compute statistics by sampling n blocks of each partition in the
RECOMPILE
invalidates program files, but if table has SIMILARITY
CHECK ENABLED, does not invalidate files compiled
with CHECK INOPERABLE PLANS.
NO RECOMPILE
does not invalidate the program files.