SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-87
Considerations—CONTROL TABLE

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.

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, suppose 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.
If you recognize such a situation you can force the use of the sequential blocksplit
algorithm by including code such as this in the server:
CONTROL TABLE SALES.CUSTOMER SEQUENTIAL BLOCKSPLIT ON;
...
INSERT INTO SALES.CUSTOMER ... ; <--- Series of inserts
...
CONTROL TABLE SALES.CUSTOMER SEQUENTIAL BLOCKSPLIT ENABLE;
Reset the SEQUENTIAL BLOCKSPLIT option to ENABLE (as in the final directive)
immediately after the sequential inserts. Using the sequential blocksplit algorithm
when inserts are not actually sequential can be extremely wasteful of disk space
and the disk process normally recognizes sequential inserts without forcing.

Local autonomy means that a query can complete without error even if some
objects or nodes that contribute to the query are unavailable. The SKIP
UNAVAILABLE PARTITION option provides local autonomy for certain situations
by directing SQL to continue processing a query even if partitions required for the
access plan of the query are not available.