Ordering Queue Table Rows - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

You may need to alter the default FIFO ordering of a queue table before consuming them. For example, in a particular application, some queue table rows might have a higher priority and must be processed immediately, while others are less important. You can change the order of rows in the queue by altering their QITS value using normal SQL INSERT and UPDATE statements.

You can decrease the QITS value for a row by moving it toward the front of the queue, closer to consumption. Conversely, by increasing the QITS value for a row, you can move it toward the rear of the queue, further away from consumption.

You can use the various peek capabilities available to you in browse mode (see Browsing a Queue Table) to determine how the existing QITS values are distributed in the queue.

To create an artificial queue position for a new row, use the SQL INSERT statement.

Rather than letting the system create the QITS value for a row by default, you instead insert it into the queue table with a QITS value that you specify. In the following example, several rows have already been inserted into a queue table named order_QT on May 25th 2004. The example request inserts a new row into the queue ahead of the existing rows by supplying a false earlier QITS value for the INSERT.

    INSERT INTO order_QT (qits, qty, item, description, price)
    VALUES ('2004-05-25 00:00:00', 1, 'RLLB1', '1 doz BASEBALLS',
             25.00);

To create an artificial queue position for a row that already exists in a queue table, use the SQL UPDATE statement.

You can either increment or decrement the QITS value for a row in 1 of 2 ways.
  • By changing the existing value of an INTERVAL constant.
  • By assigning a specific value.

The following example updates the QITS value for the row with a queue ID number of 29 by decrementing the QITS timestamp value by an INTERVAL constant.

    UPDATE order_QT
    SET qits = qits - INTERVAL '2' HOUR
    WHERE qsn = 29;

The following example updates the QITS value for the same row with a specific timestamp value.

    UPDATE order_QT
    SET qits = '2004-05-25 00:00:00'
    WHERE qsn = 29;

You can also use the Upsert form of the UPDATE statement or the MERGE statement to order queue table rows.

The following example uses the Upsert form of the UPDATE statement to increment by 3 hours the QITS timestamp value for an existing row in the order_QT table having an order number value of BZ22905789. If no such row exists, the system inserts a new row into the table using the values specified in the VALUES clause of the conditional INSERT statement.

    UPDATE order_QT
    SET qits = qits + INTERVAL '3' HOUR
    WHERE order_num = 'BZ22905789'
    ELSE
    INSERT INTO order_QT (qits, order_num, qty, item, description,
                          price)
    VALUES ('2004-05-26 00:00:00', 'BZ22905789', 1, 'RCG11',
            'Catcher Glove', 55.00);

Note that update operations on a queue table should only be part of the exception handling logic of an application and not a common occurrence. When you modify a queue table by means of an update operation, its run time cache is purged, which causes the system to perform a full-table scan of the table to rebuild that cache. The queue table run time cache is limited to approximately 20,000 row entries per PE (the exact limit is 1 MB of row entries per PE). Additional categorical limits are the following: approximately 2,000 row entries per PE and exactly 100 queue table entries per PE.

Updating rows in an unpopulated queue table has no effect on any transaction in a delayed state waiting for rows to appear in that unpopulated queue table.