SELECT AND CONSUME
Purpose
Returns data from the row with the oldest insertion timestamp in the specified queue table and deletes the row from the queue table.
Syntax
where:
|
Syntax Element … |
Specifies … |
|
select_list |
an asterisk ( * ) or a comma-separated list of valid SQL expressions. If select_list specifies *, all columns from the queue table specified in the FROM clause are returned. The select list must not contain aggregate or ordered analytical functions. |
|
queue_table_name |
the name of a queue table that was created with the QUEUE option in the CREATE TABLE statement. |
ANSI Compliance
SELECT AND CONSUME is a Teradata extension to the ANSI SQL:2011 standard.
Required Privileges
To perform a SELECT AND CONSUME from a queue table, you must have the SELECT and DELETE privileges on that table.
Locking and Concurrency
A SELECT AND CONSUME operation sets a row hash‑level WRITE lock. To upgrade to an EXCLUSIVE lock, use the LOCKING request modifier (see “LOCKING Request Modifier” on page 414).
If the request enters a delay state, the lock is not granted until after a row is inserted into the queue table.
Attributes of a Queue Table
A queue table is similar to an ordinary base table, with the additional unique property of behaving like an asynchronous first-in-first-out (FIFO) queue.
The first column of a queue table contains Queue Insertion TimeStamp (QITS) values. The CREATE TABLE request must define the first column with these data type and attributes:
TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
The QITS value of a row indicates the time the row was inserted into the queue table, unless a different, user-supplied value is inserted.
Transaction Processing Semantics
Multiple SELECT AND CONSUME statements can appear in a transaction when:
A SELECT AND CONSUME statement can consume any rows inserted into a queue table within the same transaction.
When performing SELECT AND CONSUME statements:
FIFO Ordering of Queue Table Rows Is Approximate
A queue table provides only provides an approximate FIFO ordering of its rows for these reasons:
The best practice is not to apply Teradata workload analyzer restrictions to sessions that execute SELECT AND CONSUME statements.
For information about creating workload management rules, see Teradata Workload Analyzer User Guide.
SELECT AND CONSUME Statements Operate Like a FIFO Pop Operation
SELECT AND CONSUME statements operate like a FIFO pop in these ways.
Rule for Embedded SQL
The selection form of a DECLARE CURSOR statement cannot specify a SELECT AND CONSUME statement if the preprocessor TRANSACT or -tr option is set to ANSI.
Rules and Restrictions for SELECT AND CONSUME Statements
The rules and restrictions are:
Performance Characteristics of SELECT AND CONSUME Statements
The performance of SELECT AND CONSUME statements is:
If a queue table contains no rows when a SELECT AND CONSUME statement is made on it, its containing transaction enters a delay state until one of the following occurs.
Example : Returning the Row Having the Oldest QITS Value in a Queue Table
This CREATE TABLE statement creates a queue table named shopping_cart:
CREATE SET TABLE shopping_cart, QUEUE (
cart_qits TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
product_id INTEGER,
quantity INTEGER )
PRIMARY INDEX (product_id);
This SELECT AND CONSUME statement returns all the columns of the row with the oldest value in the cart_qits column of the shopping_cart table:
SELECT AND CONSUME TOP 1 * FROM shopping_cart;
For More Information
For more information about: