SQL/MX Queuing and Publish/Subscribe Services

Major Queuing Features
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
2-15
Order by the SYSKEY Value
From Scenario 5. The Shipping Application (page B-9), this SELECT statement uses
stream access ordered by priority:
SELECT * FROM
(DELETE FROM STREAM(inbox)
FOR SKIP CONFLICT ACCESS) AS inbox
ORDER BY priority;
Because streams never return the end-of-data condition, sort operations on user-
defined column values cannot be used to sort rows returned from a stream. Instead,
the sort order must be materialized by an index. In this example, an index must be
defined on the priority column:
CREATE INDEX iinbox ON inbox (priority);
In this example, the clustering key is determined by the destination, origin, and the
system-generated SYSKEY and does not include the priority column.
Order by the SYSKEY Value
You can also define an ordered stream by the value of the SYSKEY if the table is
created with no user-defined clustering key.
From Scenario 1. The Quotes Channel (page B-2), quotes can be retrieved in the order
inserted by issuing this SELECT statement:
SELECT * FROM STREAM(quotes)
ORDER BY SYSKEY;
In this example, the clustering key is the SYSKEY.
Joins
Rows from a stream can be joined with a table if the other table is not accessed as a
stream. The plan used for such joins uses a nested loop join and always uses the
stream as the outer relation.
If you have enabled the compiler to choose the MATERIALIZE operator, as a default or
by using the CONTROL QUERY SHAPE statement, see Considerations for
MATERIALIZE, Joins, and Streams on page 4-4.
Rows returned from an embedded DELETE or UPDATE of a table or stream can also
be joined to a table. Again, the embedded DELETE or UPDATE is used as the outer
relation of a nested loop join. There is, however, a further restriction for embedded
DELETEs and UPDATEs: the join condition must use some set of columns from the
other table that have a unique constraint (either as a primary key, or a unique index).
Without this restriction, multiple rows would be returned to an application for each row
dequeued.