Queue Table Cache | CREATE TABLE | Teradata Vantage - Queue Table Cache - Advanced SQL Engine - Teradata Database

The database maintains a cache that holds row information for a number of unconsumed rows for each queue table. The system creates this queue table cache, which resides in its own Dispatcher partition, during system startup. There can be a queue table cache task on each PE in your system.

Cache row entries are shared by all queue tables that hash to that PE. Each queue table row entry is a pair of QITS and rowID values for each row to be consumed from the queue, sorted in QITS value order. The entry for a given queue table exists only in 1 queue table cache on the system. The determination of which system PE is assigned to an active queue table is made within the Dispatcher (see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142) partition by hashing its table ID using the following algorithm.

where:

For example, suppose a system has 6 online PEs and the value for tableID[1]=34.

This value points to the fifth position in the system-maintained configuration map array of online PEs, which begins its numbering at position 0. As a result of the calculation, the system assigns entries for this queue table to the cache on the fifth online PE listed in the system configuration map. If that PE goes offline, then the system reassigns the queue table to the first online PE in the configuration map.

During startup, the database allocates 64KB to the queue table cache on each PE and increases its size dynamically in 64KB increments to a maximum of 1MB per PE as required. The system initializes all the fields in the cache during startup and allocates space for 100 table entries. As queue tables are activated, they populate these slots beginning with the lowest numbered slot and proceeding upward from there. When the maximum cache size is reached, the system flushes it, either partially or entirely, depending on the number of bytes that must be inserted into the cache.

Assume the following definitions.

TotalRow Count = the number of unconsumed rows in a queue table on the AMPs.

RowCount = the number of rows in the queue table cache on a PE.

Each queue table cache entry has a maximum of approximately 2,000 row entries. As with other system maxima, this number refers to an isolated case. The actual limit on the number of row entries is determined by the size of the queue table cache. For example, 1 table can have 10 row entries, another can have 100, another can have 1,000, and so on until the limit of 1 MB is reached, at which point the system begins to flush entries from the cache. But no individual table can have more than approximately 2,000 row entries in the cache at a time.

When the maximum is reached, the system increments only the TotalRowCount value and does not add a new row entry to the cache.

When the cache reaches its maximum limits of either 100 queue table entries or 20,000 total row entries for that PE, the next incoming request triggers the flush.

The process is outlined in the following table.

1. The system attempts to flush any queue table entries marked as spoiled.
   • If there are no spoiled queue table entries, then the database checks to see if there are any pending in-queue requests or if it is in the process of collecting rows.

     If the most recent entry has pending in-queue requests or is in the process of collecting rows, then the database continues the search for an entry to flush beginning with the next lowest entry in the list.

     If the most recent entry has no pending in-queue requests or is in the process of collecting rows, then the database flushes it from the cache.

     Because it is possible to control the ordering of queue entries by inserting rows with user-selected QITS values in place of system-determined default QITS values, the entries that are flushed might not actually be those most recently added to the queue. See Ordering Queue Table Rows.

   • If there are spoiled queue table entries, then the database flushes them.
2. If no qualifying entry can be found, the system returns an error to the requestor.

When the queue table cache reaches its maximum size of 1 MB (approximately 20,000 row entries), the next incoming request triggers a flush.

The process is outlined in the following sequence of events.

1. The system attempts to flush any queue table entries marked as spoiled.

   IF there are …	THEN the database …
   no spoiled queue table entries	does the following. Checks each entry to determine if there are any pending in-queue requests or if it is in the process of collecting rows. If so, the entry is not eligible to be purged from the cache. Retrieves the cardinality of each qualifying queue table entry. Entries are ranked by their cardinality, with the largest entry being targeted for the first purge attempts. Computes the quantity of space required to insert the incoming request into the cache. Beginning with the most recently added rows in the first qualifying entry, based on their timestamp value, purges enough rows to reclaim the space needed to insert the incoming request into the queue table cache. Because it is possible to control the ordering of queue entries by means of inserting rows with user-selected QITS values in place of system-determined default QITS values (see Ordering Queue Table Rows), the entries that are flushed might not actually be those most recently added to the queue. This process continues until enough cache space is freed to accommodate the incoming request.
   spoiled queue table entries	flushes them.

2. If no qualifying entry can be found, the system aborts the transaction and returns an error to the requestor.