SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-88
Considerations—CONTROL TABLE
For example, assume that a table, CUSTOMER, has three partitions, with first keys
1, 100, and 200 in the CUSTNUM column, and these DEFINE values:
$VOL1.SALES.CUSTOMER =CUST1
$VOL2.SALES.CUSTOMER =CUST2
$VOL3.SALES.CUSTOMER =CUST3
If partition =CUST2 is unavailable at execution time, the query
>> SELECT * FROM =CUST1 WHERE CUSTNUM BETWEEN 50 AND 300;
normally fails completely. If you specify the SKIP UNAVAILABLE PARTITION
option, however, the query:

Returns rows with key values between 50 and 99.

Skips partition =CUST2.

Returns warning 8239 (Partition was skipped.) with the first row of partition
=CUST3.

Returns all rows of =CUST3 with key values up to 300.
The query proceeds successfully even if you specified the name of the
unavailable partition in the FROM clause of the statement. (The partition
named in the FROM clause must be available at compile time, however.)

When an operation is buffered, data is transferred between the file system and the
disk process, a block at a time instead of a row at a time. Buffering improves the
performance of SQL statements by reducing the number of messages exchanged
and the amount of data transferred between the file system and the disk process.
These guidelines apply to sequential block buffering operations enabled by the
SEQUENTIAL option:

Sequential INSERT buffering applies to INSERT operations performed in
sequential primary key, clustering key, or SYSKEY order.

Sequential READ buffering applies to explicit or implicit READ operations
performed in sequential primary key, clustering key, SYSKEY, or index order.
Note that buffering of READ operations can occur implicitly with a SELECT,
UPDATE, DELETE, or cursor statement.

Sequential UPDATE buffering applies to UPDATE WHERE CURRENT
operations and other UPDATE operations performed on a set of sequential
rows.

For INSERT and UPDATE operations on audited tables, any errors returned by
the disk process in flushing the buffer cause the current transaction to abort.
For nonaudited tables, errors returned by the disk process do not abort the
transaction, but might leave the table and its indexes inconsistent. In this case
SQL reports a possible loss of data by returning file-system error 122.

After a sequential INSERT or UPDATE operation has begun, any other DML
operation on the same table (directly or through a view using the same