SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-22
Single-Row Access
The INVNTRY table has these characteristics:
•
The total size of each row is 428 bytes (20 + 4 + 4 + 400).
•
The table contains 100 rows.
•
There are 90 items with a RETAIL
_
PRICE value greater than 10.
•
There is no alternate index.
•
The block size is 4096 bytes.
•
There is no slack space in the table.
This query requests the name and retail price of all items whose retail price is greater
than 10:
SELECT ITEM_NAME, RETAIL_PRICE
FROM INVNTRY
WHERE RETAIL_PRICE > 10
FOR BROWSE ACCESS ;
SQL evaluates the query by reading the table sequentially.
Single-Row Access
With single-row access, each request for a row from the file system causes a row to be
returned from the disk process, as shown in Figure 4-2.
The disk process returns each row that satisfies the predicate (RETAIL_PRICE > 10)
to the file system. To evaluate this query, the file system sends 90 messages to the
disk process. The disk process transfers 2,160 bytes of data (90 rows at 24 bytes per
row) to the file system.
Guidelines for Choosing Single-Row Access
Single-row access is chosen when either a single row or a small number of rows
satisfy the query—or when SBB is explicitly disabled by the user (by using the
CONTROL TABLE directive, described in this subsection).
If a WHERE clause matches more than one row but you are interested in only the first
row returned, set SBB off (and request single-row access) using the CONTROL
TABLE SEQUENTIAL READ OFF directive. Similarly, if you are inserting only one row,
use CONTROL TABLE SEQUENTIAL INSERT OFF to disable SBB.
Figure 4-2. Single-Row Access
PEAR 11
File System
Get Next Row Where
RETAIL_PRICE > 10
Disk Process
Returns One Physical Row
(wit h p rojection an d re striction)
5
...
11
...
20
...
PEACH
PEAR
APPLE
10
11
12
0
15
VST0402.vsd