SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-14
Evaluation of Predicates
•
If real sequential block buffering (RSBB) is chosen, the file system evaluates index
predicates.
For more information about RSBB, VSBB, and other buffering options, see Section 4,
Improving Query Performance With Environmental Options.
To review the index predicates chosen for a scan, see the EXPLAIN listing for the
query. Index predicates are noted with the title “Index pred.”
This example illustrates the use of key predicates with a table that has 100 rows:
CREATE TABLE t (a INT, b INT, c INT) ;
CREATE INDEX ti ON t (a,b) ;
INSERT INTO t VALUES (0,0,0) ;
INSERT INTO t VALUES (1,1,1) ;
INSERT INTO t VALUES (2,2,2) ;
INSERT INTO t VALUES (3,3,3) ;
...
INSERT INTO t VALUES (99,99,99) ;
SELECT * FROM t WHERE a < 4 AND b < 2 ;
SQL requests this, assuming an index access path is chosen:
•
Access to the data through index ti. The end key would specify a<4 and an index
predicate would specify b<2. Two rows of the index would qualify.
•
Access to the base table to retrieve the corresponding rows (b = 0 and
b = 1). The file system requires base-table access because of the * (all) in the
SELECT statement.
Base-Table Predicates
A base-table predicate is any predicate applied to rows in the base table.
SQL evaluates base-table predicates for every base table row within the bounds
defined by the primary begin-key and end-key predicates or for every base table row
accessed.
Base-table predicates are evaluated by the file system or the disk process, depending
on the type of I/O buffering chosen:
•
If the primary access path is chosen and real sequential block buffering (RSBB) is
used, the file system evaluates the base-table predicates.
•
If index-only access is chosen, there were no base-table predicates.
•
In all other cases, the disk process evaluates base-table predicates before
returning data to the file system.
Base-table predicates do not reduce the amount of physical I/O to the base table. If,
however, base-table predicates are evaluated by the disk process, they can reduce the
number and size of messages returned from the disk process to the file system, and