SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-24
Virtual Sequential Block Buffering (VSBB)
average number of rows per block and average percent slack space per block. Note
that it processes the whole table, so use with caution if the file is large. For more
information, see the File Utility Program (FUP) Reference Manual.
Guidelines for Choosing RSBB
RSBB is used when the disk process can do only a minimal amount of filtering
(selection and projection). For example, this query requests the whole INVNTRY table:
SELECT * FROM INVNTRY ;
In general, SQL uses RSBB when all of these are true:
•
Most rows examined satisfy all the predicates.
•
More than two-thirds of a row is to be returned.
•
More than two-thirds of the table or index is on the local node.
•
Browse access is specified or the entire table is locked with a CONTROL TABLE
directive.
Depending on the amount of slack, the optimizer might choose VSBB to reduce the
number of messages. If there is little or no slack, then VSBB offers no advantage, so
the optimizer choose RSBB if possible.
RSBB is used for SELECT operations if the table is accessed through an index from a
protection view that has a selection expression and if at least one of these is true:
•
BROWSE ACCESS is specified for the request
•
The TABLE LOCK ON option of CONTROL TABLE is specified
•
The TABLE LOCK OFF option of CONTROL TABLE is not specified and a table
lock is chosen by the optimizer
RSBB is used for UPDATE and DELETE operations if a table lock is requested and
less than 1 row per block is to be updated.
If a query accesses a protection view, index-only access implies use of RSBB if the
view has a WHERE clause. The optimizer does not use index-only access with a
protection view unless SQL can also use RSBB.
Virtual Sequential Block Buffering (VSBB)
The disk process can construct a virtual block that contains the qualified rows and
selected columns to be returned to the file system. This process is called virtual
sequential block buffering (VSBB).
Figure 4-4 on page 4-25 illustrates how the disk process does the projection and
restriction of data, which comes from several physical blocks.