SQL/MX Comparison Guide for SQL/MP Users

DML Features
HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
3-27
Query Type Comparison
Query Type Comparison
Simple data manipulation language (insert, update, delete, select) statements without
predicates, or with key equality predicates that are guaranteed to affect at most one
row, are cached in both NonStop SQL/MP and NonStop SQL/MX. An equality
predicate is cachable if one of the operands is a literal and the other operand is a fully
specified clustering (or primary) key.
Data manipulation statements with inequality predicates, nonkey predicates,
subqueries, multirow and multiway joins are cached in NonStop SQL/MP but not in
NonStop SQL/MX. Many decision support type queries are cached in NonStop
SQL/MP but not in NonStop SQL/MX.
Table 3-6 describes various statements, with examples, and indicates whether they are
cached by NonStop SQL/MP and NonStop SQL/MX:
Table 3-6. Query Type Comparison (page 1 of 3)
Type of Query
NonStop
SQL/MP
NonStop
SQL/MX
Tuple inserts:
INSERT INTO t1 VALUES(1,1,1)
YY
Insert select with key equality predicate:
INSERT INTO t1 SELECT * FROM s
WHERE key_col_a=1
YY
Insert select with column comparison predicate:
INSERT INTO t1 SELECT * FROM t2
WHERE key_col_a=key_col_b
YN
Insert select with any nonkey predicate:
INSERT INTO t1 SELECT * from t2
WHERE nonkey_col<1
YN
Insert select with subquery:
INSERT INTO t1 SELECT * FROM t2
WHERE col_a IN(SELECT col_b FROM u)
YN
Updates with key equality predicates:
UPDATE t1 SET col.a=1 WHERE key_col=2
YY
Updates with column comparison predicate:
UPDATE t1 SET col.a=1
WHERE key_col_a=key_col_b
YN
Updates with any nonkey predicate:
UPDATE t1 SET col.a=1 WHERE nonkey_col_a>2
YN
Updates with subquery:
UPDATE t1 SET a=1 WHERE col_a IN
(SELECT col_b FROM t2 WHERE col_c=1)
YN