SQL/MX Queuing and Publish/Subscribe Services

Performance Considerations
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
4-4
Considerations for MATERIALIZE, Joins, and
Streams
This example creates the workaround:
create table t1_with_dummy ( a int not null, b int, c int,
primary key(a));
insert into t1_with_dummy values (-1, 0, 0);
create view t1(a,b,c) as select a, b, c from t1_with_dummy
where a <> -1;
This example shows how to use the workaround:
exec sql declare c1 cursor with hold for select a, b, c from
(delete from stream(t1)) as t1;
Considerations for MATERIALIZE, Joins, and
Streams
The MATERIALIZE operator evaluates the query beneath it once and stores the result
of that evaluation in a temporary table, in addition to returning the result to the parent.
In SQL/MX Release 2.0, the MATERIALIZE operator is not used by default, but it can
be enabled by setting the MATERIALIZE default to ON. The compiler can be directed
to use the MATERIALIZE operator with the CONTROL QUERY SHAPE command.
The MATERIALIZE operator can be used to improve the performance of a join of a
stream with another table, if it is used to evaluate and store rows from the other table.
But if you use MATERIALIZE in this way, be aware that this can prevent new rows in
the stream from being returned to the join query, if the new row in the stream can be
joined only to a new row in the other table.