SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Enhancing SQLMX Database Performance
HP NonStop SQL/MX Installation and Management Guide—523723-004
16-7
Keeping Statistics Current
that must be changed during the operation. The lock timeout value is currently 60
seconds and cannot be changed.
In a similar way, certain other statements or commands present concurrency issues
that can affect the result of the operation. When you are duplicating, backing up, or
moving data from one object to another, these functions do not require sustained
exclusive access to the source objects. The only exclusive access involved is similar to
that required in Step 3 at the end of the function. During a DUP operation, you cannot
perform DML operations or utilities that update the data being duplicated. You can,
however, perform read operations.
Keeping Statistics Current
When you update statistics, information about a table is updated in the histogram
tables so that the information more accurately represents the current content and
structure of the SQL/MX database.
UPDATE STATISTICS collects and generates both physical and logical statistics on
SQL objects. Physical statistics refer to the index level, nonempty block count, and
EOF for partitions of SQL/MX or SQL/MP tables and indexes. Logical statistics refer to
high and low values in columns, unique entry counts in NonStop SQL/MP, and
histogram information in NonStop SQL/MX.
To generate SQL/MX physical and logical statistics on SQL/MX objects, run SQL/MX
UPDATE STATISTICS. To generate optimal physical and logical statistics on SQL/MP
tables, first run SQL/MP UPDATE STATISTICS to update SQL/MP physical statistics,
including index levels, and then run SQL/MX UPDATE STATISTICS.
For additional information about running UPDATE STATISTICS, see the SQL/MX
Reference Manual.
For information on when and why you should update statistics, see the SQL/MX Query
Guide.
Impact of Optimizer and UPDATE STATISTICS
Enhancements
Because of changes in the query optimizer and UPDATE STATISTICS for NonStop
SQL/MX Release 2.0, CONTROL QUERY SHAPE statements written for Release 1.8
might not work in the Release 2.1 or Release 2.0 environment. These changes include:
•
Replacing the MATERIALIZE operator with the ORDERED_HASH_JOIN operator.
As a result, a query plan from an earlier release that contains a MATERIALIZE
operator does not work.
•
Improved traversal of join order that speeds up query plan compilation. As a result,
some Release 1.8 query plans might be excluded from consideration by the
current optimizer.