SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-12
Using a Test Database for Emulation
If you specify the PROBABILISTIC option, SQL ignores the EXACT and SAMPLE n
BLOCKS options. The PROBABILISTIC option tells SQL to use an algorithm for
computing statistics that gives more accurate results than the algorithm used in earlier
product version updates (PVUs) of SQL/MP. Moreover, with the PROBABILISTIC
algorithm, SQL computes statistics in parallel on partitioned tables.
Statistics are collected at the table level, except for row count and nonempty block
count, which are stored on a partition-by-partition basis. Unique entry count is divided
equally among the partitions of a table, with any remainder added to the primary
partition.
For more information about the UPDATE STATISTICS statement, see the SQL/MP
Reference Manual.
Using a Test Database for Emulation
Because the SQL compiler uses statistics stored in the catalog to choose the best
access paths, you might want to create test databases that emulate larger or smaller
databases.
Normally, you update statistics so that they accurately represent the current content
and structure of the database. You can alter the statistics in a test database to emulate
a database with different statistics: for example, a production database. In this way,
you can test different database structures because the SQL compiler will use the
emulated statistics in determining the access path.
SQL/MP catalogs contain statistical information on the database tables registered in
the catalogs. You can create test databases that emulate larger or smaller databases
by altering these statistics in the test database.
Obtaining Statistics
To perform valid testing, you must have a database identical to the database you want
to test. You should create the database with identical object definitions, but you can
use different data. The statistics of the test database should match those of the
database you want to test.
To obtain the current statistics of a table, you can query the associated catalog.
These examples show how to obtain statistics on a table:
•
The index levels of each index of a table as follows:
>> VOLUME $VOL.SALES;
>> SELECT TABLENAME, INDEXNAME, INDEXLEVELS
+> FROM $VOL.SALES.INDEXES
+> WHERE TABLENAME LIKE "%$VOL.SALES.ORDERS%";
Note. Use this technique of altering statistics only in a test environment. Never manually alter
the statistics of a production database. Use the UPDATE STATISTICS statement only.