NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-83
Considerations—CONTROL TABLE
If you recognize such a situation you can force the use of the sequential blocksplit
algorithm by including code such as the following 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.
•
Getting partial query results T(local autonomy)
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.
For example, assume that a table, CUSTOMER, has three partitions, with first keys
1, 100, and 200 in the CUSTNUM column, and the following 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.)
•
Buffering for INSERT, READ, and UPDATE operations
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