SQL/MX Queuing and Publish/Subscribe Services

Major Queuing Features
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
2-14
Ordered Streams
The start and end values determine which partition of the inbox queue is accessed by
the program. To use this method, use start and end values within the range of the
destination values for a single partition.
Ordered Streams
The physical storage order of rows in a key-sequenced table is determined by the
clustering key. An application can retrieve queue entries in the order of a user-defined
clustering key or a system-generated clustering key (the SYSKEY) if there is no
user-defined clustering key. An application can also retrieve entries in the order of a
column value if a secondary index has been created for that column.
Index Requirements
If you use a secondary index to access an ordered stream, the columns in the index
definition must include all the output columns in addition to all the columns referenced
in its WHERE clause if one exists. An index automatically includes all clustering key
columns. The constraints ensure that updates to the base table cause updates to the
secondary indexes that you used for stream access.
For example, suppose that this table and index have been created:
CREATE TABLE tab1 (a int, b int, c int);
CREATE INDEX itab1 ON tab1(b, c);
In this query, the output columns are included in the index definition:
SELECT b, c FROM STREAM(tab1)
ORDER BY b;
In this query, the output columns and the columns in the WHERE clause are included
in the index definition:
SELECT b, c FROM STREAM(tab1)
WHERE c > 1 ORDER BY b;
If a secondary index is used for accessing an ordered stream with an embedded
DELETE, the columns in the index definition must include only the columns referenced
in its WHERE clause if one exists. The output columns are not required to be included
in the index definition.
In this embedded DELETE, the columns in the WHERE clause are included in the
index definition:
SELECT * FROM
(DELETE FROM STREAM(tab1)) AS tab1
WHERE c > 1 ORDER BY b;
Order by a Column Value
Ordered streams enable applications to order rows in a data stream according to user-
specified column values; for example, the price or the shipping priority of an item.