Returns data from the row with the oldest insertion timestamp in the specified queue table and deletes the row from the queue table.
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. |
SELECT AND CONSUME is a Teradata extension to the ANSI SQL:2011 standard.
To perform a SELECT AND CONSUME from a queue table, you must have the SELECT and DELETE privileges on that table.
A SELECT AND CONSUME operation sets a rowhash‑level WRITE lock. To upgrade to an EXCLUSIVE lock, use the LOCKING request modifier (see “LOCKING Request Modifier” on page 392).
If the request enters a delay state, the lock is not granted until after a row is inserted into the 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.
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:
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 in these ways.
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.
The rules and restrictions are:
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.
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 about: