SQL/MP Query Guide

ManualsBrandsHP ManualsServerHP NonStop G-Series

151

152

153

154

155

156

157

158

159

160

Improving Query Performance With Environmental

Options

HP NonStop SQL/MP Query Guide—524488-003

4-2

Keeping Statistics Current

SQL provides an UPDATE STATISTICS utility 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, it is important to have

current statistics for a table, to increase the likelihood that the optimizer will choose an

efficient plan. (For more information about selectivity, see Section 5, Selectivity and

Cost Estimates.)

You might want to run UPDATE STATISTICS after loading or re-creating a table, after

structural changes such as creation of an index, or after significant update activity

(growth in database size). Before running UPDATE STATISTICS, however, you should

consider the following:

•

If you experience performance degradation, check for fragmentation of blocks. Use

the FILEINFO command with the STATISTICS option set on. If blocks are

fragmented, running UPDATE STATISTICS and recompiling the queries does not

help; first reload the table online by using the FUP RELOAD command.

•

Run UPDATE STATISTICS only after a table has been loaded with data. Do not

run UPDATE STATISTICS when a table is empty.

•

Depending on the size of the table, updating statistics can take longer than you

would like; therefore, run UPDATE STATISTICS during off hours when peak

performance is not required. You can determine the effect of UPDATE STATISTICS

on a production query by bracketing UPDATE STATISTICS and EXPLAIN on the

queries in a transaction.

•

First determine the effect of the UPDATE STATISTICS statement by issuing the

statement within a TMF transaction. You can then back out the operation if

necessary. In an SQLCI session, do the following:

Issue a BEGIN WORK statement; then issue UPDATE STATISTICS with the

NO RECOMPILE option.

Use EXPLAIN to see if the new statistics would give you the better query

execution plan.

Depending on the EXPLAIN output, you can decide whether to commit the

transaction (COMMIT WORK) or back out the transaction (ROLLBACK

WORK).