Problem Statement - Teradata Database

Problem Statement

The current definition of sales_table does not use row partitioning.

     CREATE TABLE sale_stable (

       product_code        CHARACTER(8),

       sales_date          DATE,

       agent_id            CHARACTER(8),

       quantity_sold       INTEGER,

       product_description VARCHAR(50))

     PRIMARY INDEX (product_code,sales_date,agent_id);

The DBA has been told to speed up the ad hoc queries and agent analysis queries. He considers two possible optimizations, neither of which uses row partitioning.

Define either a UPI or a join index on the transaction date column.

The DBA then sets up tests for both scenarios. Unfortunately, the EXPLAIN reports show that the optimizer finds neither index to be selective enough to improve performance over a full-table scan, and does not use them.

Split the table into 25 separate tables, each containing transactions for a calendar month, and then define a view that UNIONs all the tables. This view is intended to be used by the applications that analyze 24 months of sales history.

After some analysis, he concludes that this solution could indeed speed up the targeted queries, but that it also adds too much complexity for his end users, who would now have to understand the view structure and change the table names in their queries, code more complicated UNION statements, and select appropriate date and product code ranges. The requirement to know the right table name also applies to the short‑running single‑AMP queries that specify primary index values. This proposed solution also complicates the nightly load jobs, especially in the first few days of a month when a small number of the transactions would be from the prior month, as well as complicating his long successful archive strategy. The DBA ultimately rejects this alternative as being too complicated and error-prone.

With these negative results in hand, the DBA next considers redefining the sales table with row partitioning.