SQL Operations on Queue Tables | VantageCloud Lake - SQL Operations on Queue Tables - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

SQL interfaces provide support for FIFO push, pop, and peek operations on queue tables as indicated in the following table.

FIFO Operation Supporting SQL Statement
Push INSERT
Pop SELECT AND CONSUME TOP 1
Peek SELECT
SELECT AND CONSUME is only supported on the Block File System on the primary cluster. It is not available for the Object File System.

The different operative modes performed by the two forms of the SELECT statement are called consume and browse modes, respectively. You cannot use either the PERCENT or WITH TIES options with consume mode SELECT operations on a queue table. You must specify 1 as the value for n in the TOP n specification.

SELECT Statement Function Performed on Queue Table
SELECT AND CONSUME TOP 1 Consume
SELECT Browse

Consume mode rows are FIFO-ordered unless otherwise explicitly ordered (see Ordering Queue Table Rows) and browse mode rows use the standard SQL ordering mechanisms such as an ORDER BY clause.

Vantage supports the following DML statements for embedded SQL processing of queue tables.
  • DELETE
  • INSERT
  • MERGE
  • SELECT
  • SELECT AND CONSUME
  • UPDATE

SELECT AND CONSUME is not supported for positioned cursors. You cannot use consume mode SELECT statements in ANSI session mode, because all ANSI mode cursors are, by default, positioned cursors. See Positioned Cursors and Teradata® Preprocessor2 for Embedded SQL Programmer Guide, B035-2446 for further information.

Vantage provides queue table DML support for all the following forms of embedded SQL.
  • Static
  • Dynamic
  • Static cursor
  • Dynamic cursor

Do not perform DELETE, MERGE, and UPDATE statements frequently on a queue table. These operations spoil the FIFO cache for the table entries, and Vantage must rebuild the FIFO cache, performing a full-table scan to do so.

Instead, reserve these statements for exception handling.

For more information about using these statements with queue tables, see the following:

Because queue tables are persistent database objects in the database, the basic Teradata features for transactions are provided for them. Like other database transactions, queue table transactions have the ACID properties of atomicity, consistency, isolation, and durability. See Transaction Processing for further information about Teradata transactions.