SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-29
Controlling the Opening of Tables, Views, and
Indexes
Controlling the Opening of Tables, Views, and
Indexes
The NonStop SQL/MP file system opens (grants access to) objects when directed to
do so by the NonStop SQL/MP executor. Tables, views, and indexes, and partitions of
these objects, are usually opened on demand. SQLCI users or application programs
do not influence when tables, views, and indexes are opened or closed.
With the open-on-demand feature, each object or partition is opened during the
processing of individual SQL statements. When many statements require the opening
of many different tables, views, and indexes used as access paths, or partitions of
these objects, this feature can require additional time at the first execution of individual
statements.
You can, however, control this overhead by using the CONTROL TABLE object-
name OPEN directive to override open-on-demand. When the CONTROL TABLE
directive is processed, one or more objects can be opened immediately. You can use
this directive to open all tables, views, indexes, and any partitions of these objects as
soon as a program is started.
This approach shifts the processing time for opening objects to the beginning of
program execution and away from the times when the individual statements that
require the objects are processed. After an object is opened, it stays open until the
program is stopped.
When you enter CONTROL TABLE table-name OPEN ALL PARTITIONS, the table
and all of its partitions and indexes are all opened.
When you enter CONTROL TABLE view-name OPEN ALL, the view and the
underlying tables and indexes are opened.
Caution. Use the CONTROL TABLE statement with the OPEN ALL option only if all these are
true:
•
All open activities must occur when the program starts (add a “dummy” call to the cursor
during initialization).
•
The object containing the cursor eventually accesses all partitions.
•
The plan for the cursor is not a parallel plan.