Queue Tables / SELECT AND CONSUME TOP N

Database
Enthusiast

Queue Tables / SELECT AND CONSUME TOP N

Hi all,
I remember that there is the concept of Queue tables in Teradata V2R6, using which we can run a SELECT AND CONSUME TOP 1 from tablename. But i dont seem to get the syntax correct, nor am i able to get how to create a queue table. Also, any performace implications related to queue tables would be highly appreciated.

Thanks.
2 REPLIES
Enthusiast

Re: Queue Tables / SELECT AND CONSUME TOP N

Just managed to get the create table syntax correct, and created the following queue table:

create table quetable2, queue
(create_timestamp timestamp(6) not null default current_timestamp(6), a int, b int)
;

However, can anyone please throw more light on the utility of queue tables, and also the limit on the data load that they can handle.

Thanks.
Enthusiast

Re: Queue Tables / SELECT AND CONSUME TOP N

See "Performance Issues for Queue Tables" in pdf: "SQL Data Definition Language" for the database version you are looking at. Some statements re performance state:

The expected behavior for a queue table is that the producers of queue table rows are

working at approximately the same speed as their consumers: rows are consumed as

quickly as they are inserted into the table.

In other words, an application that would typically populate a queue table with millions of

rows before consuming any of them would not see any performance gain over using a

nonqueue table, so is not a good candidate for using the feature.

When the number of queue table rows on a single PE frequently exceeds the size of the

queue table cache, which is approximately 2,000 row entries per table or 20,000 row

entries per PE53, then the system must perform full-table scans more frequently to refresh

the cache. Such states also increase the likelihood of errors related to lock-oriented

transaction aborts and queue table cache overflow being returned to the requestor.