Oracle AQ dequeue order

Question

A trigger in an Oracle 10g generates upsert and delete messages for a subset of rows in a regular table. These messages consist out of two fields:

• A unique row id.
• A non-unique id.

When consuming these message I want to impose an order on the deque process that respects the following constraints:

• Messages must be dequeued in insertion order.
• Messages belonging to the same id must be dequeued in such a fashion that no other dequeuing process should be able to dequeue a potential successor message (or messages) with this id. Since the messages are generated using a trigger I cannot use groups for this purpose.

I am using the Oracle Java interface for AQ. Any pointers on how that could be achieved?

Answer 1

The default dequeue order I believe is first in first out, therefore they will be dequeued in the same order they were enqueued.

For your second point, are you saying that you want to serialize dequeue on the non-unique-id? Ie, you basically have many queues within your queue, and you only want one job to consume messages form each queue at any one time?

Ie, you have messages:

1 | a
2 | a
3 | b
4 | a

Here you have two types of record (a and b) and you want 1 job to consume all the a's and another to consume all the b's. If that is the case consider creating multiple queues perhaps?

Failing multiple queues, have a look at the dequeue_options_t type that you pass to the dequeue procedure - most notably dequeue_condition - this allows you to select only specific messages, so you could start a job for all the a's and another for all the b's etc.