Selection of a primary index might or might not be important when defining a queue table (see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 for information about creating and using queue tables). If you do not define a primary index explicitly, the system uses the QITS (Queue Insertion Time Stamp) column, the first column in the queue table, as the default. The values of the QITS column reflect the time of insertion of their rows. If you need to perform a primary index update or delete one row from a queue table, you can, in most cases, easily browse the table and get the value of the QITS and other columns needed for the single-AMP update activity.
However, you might also choose a primary index column set based on the input data that is easily known, to support frequent updating. The queue table definition presented below has orderkey as it primary index, for example.
CREATE TABLE event02_QT, QUEUE ( table_event02_QT_QITS TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), orderkey DECIMAL(18,0) NOT NULL, productkey DECIMAL(18,0) NOT NULL) PRIMARY INDEX (orderkey);
Using a business entity for the primary index makes sense if you have a requirement to reorder the queue on a regular basis (or otherwise manipulate the rows), and the number of rows it holds is not trivial, making browsing the entire queue table for each update less desirable.
When you perform INSERT … SELECT processing from a staging table into a queue table, you must pay closer attention to the selection of the primary index for the queue table. Such an INSERT … SELECT operation might perform a similar function as a trigger when doing row-at-a-time insert operations: select the few rows that compose events and insert them immediately into a queue table for further processing.
If you are using a minibatch approach to loading data, then using set processing to identify events makes sense. In this case, the WHERE clause for the INSERT … SELECT statement contains the event-identification criteria. In the case illustrated by the previous figure, having a queue table with the same primary index definition as the staging table improves the efficiency of the INSERT … SELECT processing. The two inserts into the staging and queue tables then occur on the same AMP, eliminating the overhead of row redistribution.
Designing a queue table to share the same primary index as a another base table could also be useful for tactical queries (see Design Issues for Tactical Queries). A given tactical application might want either to peek into the queue or to seek out the presence of a specific row in the queue. If the primary index value of the other table is known, and it is also the primary index value of a possible queue table row, then the system enables single-AMP access.
A similar situation exists if Teradata Parallel Data Pump inserts are made into a target table that has a trigger into a queue table. If the queue table and the target table share the same primary index, and you want to serialize the input to avoiding cross-session blocking, serializing on the base table primary index also causes inserts into the queue table to be serialized effectively.