Ordering Queue Table Rows - Teradata VantageCloud Lake

You may need to alter the default FIFO ordering of a queue table before consuming its rows. For example, in one application, there may be queue table rows with a higher priority that must be processed immediately, while other queue table rows 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 the row toward the front of the queue, closer to consumption. Conversely, by increasing the QITS value for a row, you can move the row toward the rear of the queue, further away from consumption.

You can use the 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 insert the desired QITS value into the queue table. In the following example, rows have been inserted into a queue table 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.

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);

Update operations on a queue table must 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 runtime cache is cleared, which causes the system to perform a full-table scan of the table to rebuild that cache. The queue table runtime 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.