Selection of a primary index may be important when defining a queue table (see CREATE TABLE (Queue Table Form)). 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 browse the table and get the value of the QITS and other columns needed for the single-AMP update activity.
However, you may also choose a primary index column set based on the input data that is easily known, to support frequent updating. The following queue table definition has orderkey as its 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 the queue 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 may perform a similar function as a trigger when doing row-at-a-time insert operations: select the few rows that compose events and insert those rows 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. 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 can also be useful for tactical queries (see Design Issues for Tactical Queries). A given tactical application may want to peek into the queue or seek the presence of a specific row in the queue. If the primary index value of the other table is known, and 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.