NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-78
CONTROL TABLE Directive
SEQUENTIAL [ INSERT | READ | UPDATE ] { ON | OFF | ENABLE }
specifies whether the file system should buffer INSERT, READ, or UPDATE
operations. Buffering reduces the number of messages between the file system and
disk process by a factor equal to the blocking factor.
For audited tables, the default is ENABLE. For nonaudited tables, the default is OFF
for INSERT and UPDATE; the default is ENABLE for READ.
Use OFF if operations will not be sequential or if a small but common subset of
rows will be accessed concurrently by more than one process. Use ENABLE if you
are unsure whether operations will be sequential or random.
Under certain conditions, SQL might determine whether or not to buffer operations
regardless of the option you specify. For more information about this and other
aspects of buffering, see the subsection Buffering for INSERT, READ, and
UPDATE operations on page C-83 following the Considerations subsection of this
entry.
SEQUENTIAL BLOCKSPLIT [ FOR INSERT ] { ON | ENABLE }
specifies the method of splitting blocks when an INSERT requires a block split:
The default is SEQUENTIAL BLOCKSPLIT ENABLE.
The SEQUENTIAL BLOCKSPLIT option is similar to the SETMODE 91,3 option
available for Enscribe files, except that it applies only to tables and views, not to
indexes.
{ SKIP | STOP AT } UNAVAILABLE PARTITION
controls whether SQL continues to process a query when a partition required by the
access plan of the query is unavailable.
ON buffer operations when possible
OFF do not buffer operations
ENABLE let SQL decide when to buffer
ON splits blocks as if inserts are sequential
ENABLE selects the block split algorithm depending on whether sequential
inserts are detected
Note. Using SEQUENTIAL BLOCKSPLIT ON when inserts are not actually sequential and
in increasing order by primary key (or when intervening records exists in blocks where
inserts occur) can waste substantial disk space.
Use SEQUENTIAL BLOCKSPLIT ENABLE unless you are extremely knowledgeable
about the way block splits are handled and absolutely certain that your table will receive a
series of sequential inserts that the disk process can not recognize as sequential. (See
Specifying the Sequential Blocksplit Algorithm under Considerations—CONTROL TABLE
on page C-80 for a discussion of such a case.)