NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-82
Considerations—CONTROL TABLE
Make sure you are familiar with details of query operations as described in the
NonStop SQL/MP Query Guide, and be sure to restore default values for these
options immediately afterwards. For example:
CONTROL TABLE * ACCESS PATH SYSTEM;
CONTROL TABLE * JOIN METHOD SYSTEM JOIN SEQUENCE SYSTEM;
Also, be aware of these special considerations:
°
If you specify an access path, a query does not run unless that access path is
available. To allow for alternate paths, code your application to check for errors
and specify an alternate path if the normally-preferred path is not available.
(SQL automatically considers alternate access paths if you do not specify an
access path.)
°
Certain errors in the specification of ACCESS PATH, JOIN METHOD, or JOIN
SEQUENCE cannot be detected and reported until an affected DML query is
compiled. For example, if you erroneously specify a JOIN SEQUENCE greater
than the number of tables in the next SELECT that includes the table, an error
occurs in response to the statement that contains the SELECT, not to the
CONTROL TABLE directive.
°
If an index for an UPDATE includes a column being updated, such as
UPDATE table SET index-column = index-column + 1
specifying that index as an access path can cause an operation that never ends
(the “Halloween problem” in database literature).
SQL issues an error message if it compiles an UPDATE statement that can lead
to the Halloween problem and a CONTROL TABLE directive prevents it from
selecting an alternate access path.
EXPLAIN reports indicate whether ACCESS PATH, JOIN METHOD, and JOIN
SEQUENCE were forced by the user rather than determined by SQL.
•
Specifying the sequential blocksplit algorithm
Normally, the disk process automatically optimizes for sequential or nonsequential
inserts by changing its block splitting algorithm for a given table open when it
recognizes a series of sequential inserts. In a few unusual cases, however, the disk
process fails to recognize sequential inserts.
For example, imagine that each of two separate clients sends a series of sequential
records to the same server for insertion in a table. Client A sends records with
primary key values 1, 2, 3, and so forth. Client B sends records with primary key
values 1001, 1002, 1003, and so forth. The server then sends the interleaved stream
of records (1, 1001, 2, 1002, 3, 1003 ...) to the disk process for insertion in the table.
Because the series of records sent by the server is not sequential, the disk process
fails to recognize that sequential inserts are occurring (although in two separate
sequences within the table) and does not change the block splitting algorithm
accordingly.