The performance of SELECT AND CONSUME statements is:
- Similar to that of selecting a single row from a base table using a USI.
- More expensive than selecting a single row from a base table using a primary index.
If a queue table does not contain rows when you perform a SELECT AND CONSUME statement, its containing transaction enters a delay state until one of the following occurs:
- A row is inserted into the queue table.
- The transaction aborts as a result of one of these reasons.
- Direct user intervention, such as an ABORT statement
- Indirect user intervention, such as a DROP TABLE statement on the queue table