17.05 - Performance Characteristics of SELECT AND CONSUME Statements - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)
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