Inserting Rows into Queue Tables - Analytics Database - Teradata Vantage

SQL Data Manipulation Language
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-12-13
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

The first column of a queue table is defined as a Queue Insertion TimeStamp (QITS) column. The values in the column determine the order of the rows in the queue, resulting in approximate first-in-first-out (FIFO) ordering.

If you want the QITS value of a row to indicate the time that the row was inserted into the queue table, then you can use the default value, the result of CURRENT_TIMESTAMP, instead of supplying a value. If you want to control the placement of a row in the FIFO order, you can supply a TIMESTAMP value for the QITS column.

For a multistatement request containing multiple INSERT requests that do not supply values for the QITS column, the QITS values are the same for every row inserted.

If you want unique QITS values for every row in a queue table, you can do any of the following things:
  • Supply a TIMESTAMP value for the QITS column in every INSERT request.
  • Avoid multistatement requests containing multiple INSERT statements that do not supply values for the QITS column.
  • Add incremental offsets to the current timestamp for the QITS column value in each INSERT request.

    For example:

         INSERT shopping_cart(CURRENT_TIMESTAMP + INTERVAL '0.001', 100)
    ;INSERT shopping_cart(CURRENT_TIMESTAMP + INTERVAL '0.002', 200)
    ;INSERT shopping_cart(CURRENT_TIMESTAMP + INTERVAL '0.003', 300);
Regarding performance, an INSERT operation into a queue table has the following effects:
  • Does not affect response time when the system is not CPU-bound.
  • Is more expensive than an INSERT into a base table because it requires the update of an internal in-memory queue.

For details on queue tables and the queue table cache, see CREATE TABLE in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

Inserting Into Queue Tables Using Iterated Requests

If you use an INSERT request in an iterated request to insert rows into a queue table, you might have to limit the number of data records with each request to minimize the number of rowhash-level WRITE locks placed on the table and reduce the likelihood of deadlocks occurring because of resource conflicts between the locks and the all-AMPs table-level READ lock exerted by the internal row collection processing used by queue tables to update the internal queue table cache.

IF you use an INSERT request in an iterated request to insert rows into a queue table and … THEN …
all of the following conditions are true:
  • the queue table is not empty
  • either an INSERT request or a SELECT AND CONSUME request has already been performed from the queue table since the last system reset
  • rows are not updated or deleted from the queue table during the insert operation
 the number of data records that you pack with each request is not an issue.
any of the following conditions are true:
  • the queue table is empty
  • neither an INSERT request nor a SELECT AND CONSUME request has already been performed from the queue table since the last system reset
  • rows are updated or deleted from the queue table during the insert operation
 pack a maximum of four data records with each request.

For example, if you use BTEQ to import rows of data into a queue table, use a maximum value of 4 with the BTEQ .SET PACK command.

These conditions trigger the internal row collection processing used by queue tables to update the internal queue table cache.

For details on queue tables and the queue table cache, see CREATE TABLE in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.