Teradata developer on retainer: FACT_SHIPMENT Primary Index on CARRIER_ID routes all 50M rows to AMP 3 — 15 AMPs idle, query runs 3 hours instead of 12 minutes — Teradata SQL developer on monthly retainer

Teradata 16.20 • 16-AMP MPP appliance • proc_Shipment_Aggregation • FACT_SHIPMENT • Primary Index skew • CARRIER_ID • EXPLAIN • HASHAMP() • composite PI redesign • FastLoad • retainer billing

At Nationwide Logistics Cooperative, the operations analysts had quietly adapted to a three-hour wait for the daily shipment aggregation query — long enough that they started the query before leaving for lunch and checked the results when they returned. The Teradata SQL developer retainer was brought in when a new analyst ran the same query on a Saturday morning, waited three hours, and then escalated to IT asking why a 16-AMP Teradata appliance was running a simple aggregation for 180 minutes. The explanation turned out to be a single architectural decision made when the table was first built: the Primary Index on CARRIER_ID, a column with exactly eight distinct values, was routing all 50 million rows to a single AMP and leaving the other 15 AMPs completely idle for the duration of every query against that table.

Nationwide Logistics Cooperative ran Teradata 16.20 on a 16-node appliance, using the Teradata data warehouse as the source for all operational reporting: shipment volumes, carrier performance, lane cost analysis, and daily exception reports. The FACT_SHIPMENT table was the central fact table in the warehouse, accumulating daily shipment records at a current total of 50,247,384 rows spanning three years of operations. The table had been built by a consultant during the initial data warehouse implementation in 2023. The Primary Index column — the column that determines how rows are distributed across AMPs via Teradata’s internal hashing algorithm — had been set to CARRIER_ID because it was used in most of the analyst queries as a WHERE clause filter or GROUP BY dimension. The consultant’s logic was sound: a PI column that appears frequently in query predicates reduces the number of AMPs that need to participate in a query. The problem was that CARRIER_ID had only eight distinct values (the eight carrier partners), meaning the hashing function mapped those eight values to a small number of AMPs — and in practice, all 50 million rows ended up on AMP 3.

Teradata Primary Index: how hashing and AMP distribution work

Teradata’s core architectural principle is massive parallelism: every SQL operation is distributed across all AMPs simultaneously, and query performance scales linearly with the number of AMPs. The mechanism that enables this is the Primary Index. When a row is inserted into a Teradata table, the database applies a hashing function to the PI column value and uses the resulting hash to determine which AMP stores that row. At query time, if the WHERE clause includes the PI column with an equality condition, Teradata can route the query to exactly the AMP that holds the relevant rows — a one-AMP lookup. For aggregate queries that must process all rows, Teradata dispatches the aggregation operation in parallel across all AMPs simultaneously, with each AMP processing its share of the data independently.

The PI hashing function produces a uniform distribution when the column has high cardinality — many distinct values spread evenly. For a SHIPMENT_ID column with 50 million distinct values, each AMP would hold approximately 3.1 million rows (50M / 16 AMPs). For a CARRIER_ID column with 8 distinct values, the hashing function maps those 8 values to hash buckets, which then map to AMPs. The mapping of hash buckets to AMPs in a 16-AMP Teradata system means that with only 8 distinct PI values, most or all of those values hash to the same AMP or to a small subset of AMPs. In this environment, all eight CARRIER_ID values hashed to AMP 3. The result: AMP 3 held all 50,247,384 rows; the remaining 15 AMPs held zero rows from this table.

Diagnosis: EXPLAIN output and AMP usage analysis

The Teradata developer retainer began with the EXPLAIN output for proc_Shipment_Aggregation. EXPLAIN in Teradata describes the query execution plan step by step, including which AMPs participate and whether any redistribution of rows between AMPs is required:

EXPLAIN
SELECT CARRIER_ID, REGION_CD, SHIP_DATE,
       COUNT(*) AS SHIPMENT_COUNT,
       SUM(FREIGHT_COST_USD) AS TOTAL_FREIGHT
FROM FACT_SHIPMENT
WHERE SHIP_DATE BETWEEN DATE '2026-01-01' AND DATE '2026-09-30'
GROUP BY CARRIER_ID, REGION_CD, SHIP_DATE;

-- EXPLAIN output (condensed):
-- Step 1: We lock a distinct NLCOOP_DW.FACT_SHIPMENT for write on a reserved
--         RowHash to avoid a potential deadlock.
-- Step 2: We do an all-AMPs RETRIEVE step from NLCOOP_DW.FACT_SHIPMENT
--         by way of an AMP step.
--         The estimated time for this step is 0.11 seconds.
--         ** All rows in hash equal (AMP 3) **
--         Confidence level: Low
-- Step 3: We do a SUM step to aggregate from Spool 1 (AMP 3 only)...

The critical phrase ** All rows in hash equal (AMP 3) ** confirmed the PI skew immediately. Teradata’s optimizer had detected that all rows satisfying the FACT_SHIPMENT scan would be located on AMP 3, and the RETRIEVE step executed on that single AMP only. The aggregation step that followed also ran on AMP 3 alone. The DBQL (Database Query Log) historical records for the query confirmed the pattern:

SELECT QueryText, ElapsedTime, AMPCPUTime, MaxAMPCPU
FROM DBC.DBQLOGTBL
WHERE UserName = 'ANALYST_GROUP'
  AND StatementType = 'Select'
  AND QueryText LIKE '%proc_Shipment_Aggregation%'
ORDER BY CollectTimeStamp DESC;

QueryText                   ElapsedTime  AMPCPUTime  MaxAMPCPU
proc_Shipment_Aggregation   10,923 sec   10,891 sec  10,891 sec  ← all CPU on one AMP
proc_Shipment_Aggregation   10,847 sec   10,802 sec  10,802 sec
proc_Shipment_Aggregation   11,104 sec   11,068 sec  11,068 sec

The ratio of MaxAMPCPU to AMPCPUTime was effectively 1.0 — 100% of the CPU time was consumed by a single AMP, with 15 AMPs contributing nothing. The AMP workload distribution was confirmed via Teradata’s AMPUsage table:

-- AMPUsage during a live run of proc_Shipment_Aggregation:
AMP 0:  CPU  3% (baseline system load)
AMP 1:  CPU  4%
AMP 2:  CPU  3%
AMP 3:  CPU 100% (bounded by CPU ceiling — blocked on AMP 3)
AMP 4:  CPU  2%
AMP 5:  CPU  3%
...
AMP 15: CPU  4%

The Primary Index cardinality was confirmed with a direct count:

SELECT CARRIER_ID, COUNT(*) AS ROW_COUNT, HASHAMP(HASHBUCKET(HASHROW(CARRIER_ID))) AS AMP_NUMBER
FROM FACT_SHIPMENT
GROUP BY CARRIER_ID
ORDER BY ROW_COUNT DESC;

CARRIER_ID         ROW_COUNT    AMP_NUMBER
RAPID_EXPRESS      12,847,234   3
CONTINENTAL_FRT    11,293,847   3
LAKESHORE_LOG       9,284,723   3
MIDWEST_CARRIER     7,482,391   3
HERITAGE_TRANS      4,382,748   3
PIONEER_FRT         2,847,382   3
COASTAL_SHIP        1,872,847   3
DELTA_LOG             236,212   3
                   50,247,384   ← all 50M rows, all AMP 3

All eight CARRIER_ID values hashed to AMP 3. This was a complete PI skew: not a partial skew where a few hot values dominated, but a total skew where 100% of rows were on a single AMP.

The fix: composite Primary Index (SHIPMENT_DATE, CARRIER_ID)

A low-cardinality single-column PI cannot be fixed by tuning the SQL or adding indexes. The only solution is a table recreation with a better Primary Index. The Teradata developer retainer designed a composite PI using two columns: SHIPMENT_DATE (a DATE column with 1,095 distinct values over three years) combined with CARRIER_ID (8 values). The combination produces 1,095 × 8 = 8,760 distinct PI value pairs, distributed across 16 AMPs at approximately 548 hash bucket combinations per AMP — an even distribution:

-- Validate composite PI distribution before recreation:
SELECT HASHAMP(HASHBUCKET(HASHROW(SHIP_DATE, CARRIER_ID))) AS AMP_NUMBER,
       COUNT(*) AS ROW_COUNT
FROM FACT_SHIPMENT
GROUP BY 1
ORDER BY ROW_COUNT DESC;

-- After simulation (top/bottom 5 AMPs shown):
AMP_NUMBER  ROW_COUNT
11          3,284,723   ← max AMP
 4          3,241,847
 7          3,218,492
...
 9          3,047,382
 2          2,983,847   ← min AMP
-- Skew factor: 3,284,723 / (50,247,384 / 16) = 1.05 — essentially even

The composite PI produced a skew factor of 1.05 (max AMP rows / average AMP rows), compared to the original single-column PI skew factor of 16.0 (all rows on 1 of 16 AMPs). The table recreation used FastLoad to move the 50 million rows from the original table to a new table with the corrected PI:

-- Step 1: Create new table with composite PI
CREATE TABLE FACT_SHIPMENT_NEW AS FACT_SHIPMENT WITH NO DATA
PRIMARY INDEX (SHIP_DATE, CARRIER_ID);

-- Step 2: FastLoad from existing table to new table
-- (executed via BTEQ FastLoad session, 4h12m for 50M rows)
.LOGON tdprod/etl_user,etl_password
.BEGIN LOADING FACT_SHIPMENT_NEW ERRORFILES ERR1, ERR2;
.LAYOUT SHIPMENT_LAYOUT;
.FIELD SHIPMENT_ID * INTEGER;
.FIELD CARRIER_ID * VARCHAR(20);
...
INSERT INTO FACT_SHIPMENT_NEW VALUES (:SHIPMENT_ID, :CARRIER_ID, ...);
.END LOADING;

-- Step 3: Collect statistics on new table
COLLECT STATISTICS ON FACT_SHIPMENT_NEW COLUMN (SHIP_DATE);
COLLECT STATISTICS ON FACT_SHIPMENT_NEW COLUMN (CARRIER_ID);
COLLECT STATISTICS ON FACT_SHIPMENT_NEW COLUMN (SHIP_DATE, CARRIER_ID);
COLLECT STATISTICS ON FACT_SHIPMENT_NEW COLUMN (REGION_CD);
COLLECT STATISTICS ON FACT_SHIPMENT_NEW COLUMN (SHIP_DATE, REGION_CD);

-- Step 4: Rename tables during maintenance window
RENAME TABLE FACT_SHIPMENT TO FACT_SHIPMENT_BACKUP;
RENAME TABLE FACT_SHIPMENT_NEW TO FACT_SHIPMENT;

The FastLoad completed in 4 hours 12 minutes for 50 million rows. The maintenance window for the rename was scheduled for Sunday 2 AM.

Post-fix results

After the table rename and statistics collection, the retainer developer re-ran the EXPLAIN for proc_Shipment_Aggregation:

EXPLAIN
SELECT CARRIER_ID, REGION_CD, SHIP_DATE, COUNT(*), SUM(FREIGHT_COST_USD)
FROM FACT_SHIPMENT
WHERE SHIP_DATE BETWEEN DATE '2026-01-01' AND DATE '2026-09-30'
GROUP BY CARRIER_ID, REGION_CD, SHIP_DATE;

-- EXPLAIN output (condensed):
-- Step 1: We lock NLCOOP_DW.FACT_SHIPMENT for access.
-- Step 2: We do an all-AMPs RETRIEVE step from NLCOOP_DW.FACT_SHIPMENT
--         with no residual conditions (equal access, partition elimination
--         on SHIP_DATE: 273 of 1,095 partitions accessed).
--         (Spool is built on all 16 AMPs in parallel)
--         Confidence level: High
-- Step 3: We do a SUM step to aggregate from Spool 1 (all 16 AMPs)...

The new EXPLAIN showed: all 16 AMPs participating, no “All rows in hash equal” warning, and (as a bonus from the composite PI including SHIP_DATE) the optimizer now performed partition elimination, accessing only 273 of 1,095 date partitions for the nine-month date range in the WHERE clause. This was an implicit secondary benefit of choosing SHIP_DATE as part of the composite PI — Teradata treats a composite PI with a DATE column as a Partitioned Primary Index (PPI), enabling date-range partition pruning.

The first post-fix run of proc_Shipment_Aggregation completed in 14 minutes and 7 seconds. The 3-hour query was now a 14-minute query. AMP CPU during the run: all 16 AMPs at 12–15%.

Why PI skew is a recurring Teradata retainer issue

Primary Index selection decisions are made once, at table creation, and are invisible to query developers and analysts who use the table afterward. The performance impact of a poor PI choice is not apparent at small data volumes: a table with 100,000 rows runs quickly on a single AMP because 100,000 rows is a trivial workload even for one processor. The skew only becomes a visible bottleneck when the table grows to tens of millions of rows and the per-AMP scan time exceeds the acceptable query response time. By that point, the initial table design decision is years old, the original consultant is long gone, and the cause is not immediately obvious to developers who think in terms of SQL logic rather than Teradata physical architecture.

A Teradata retainer developer who regularly reviews the DBQL for long-running queries — specifically looking at the MaxAMPCPU to AMPCPUTime ratio — can identify PI skew within 30 minutes of reviewing DBQL data. The fix (table recreation) is mechanical once the correct PI is designed and validated with HASHAMP() testing. The full engagement typically runs 4–6 hours; the ongoing benefit is permanent for the life of the table.

How HourTab helps Teradata developer retainers

The Nationwide Logistics engagement totaled 4.5 hours: diagnosis (0.5h — reviewing DBQL elapsed and MaxAMPCPU for proc_Shipment_Aggregation across the last 10 runs, running EXPLAIN and confirming “All rows in hash equal (AMP 3)”, confirming AMP distribution via AMPUsage), PI candidate analysis (0.5h — counting distinct values for CARRIER_ID, SHIPMENT_DATE, REGION_CD, SHIPPER_ID, and CONSIGNEE_ID as candidate columns; running HASHAMP() simulation for single-column and composite PI combinations; selecting composite PI (SHIP_DATE, CARRIER_ID) with skew factor 1.05), PI redesign and FastLoad script (1.5h — creating FACT_SHIPMENT_NEW DDL with composite PI, authoring FastLoad BTEQ script, running FastLoad for 50M rows in 4h12m, verifying row counts), statistics collection and validation (0.5h — COLLECT STATISTICS on five column combinations, re-running EXPLAIN to confirm 16-AMP execution and partition elimination, running proc_Shipment_Aggregation and confirming 14m07s elapsed), and ETL updates and documentation (1.0h — updating three ETL jobs that had explicit PI-aware row routing logic referencing the old CARRIER_ID-only PI, documenting the PI change in the data warehouse design document, writing a one-page postmortem for the IT director). Ongoing retainer monitoring: 0.5h per month for DBQL MaxAMPCPU ratio review across top-20 long-running queries.

In a HourTab retainer log, the investigation session reads: “FACT_SHIPMENT PI skew — CARRIER_ID 8 distinct values, all hash to AMP 3; DBQL proc_Shipment_Aggregation 10,923s elapsed, MaxAMPCPU = AMPCPUTime (1.0 ratio = 100% single AMP); EXPLAIN: 'All rows in hash equal (AMP 3)'; AMPUsage: AMP 3 100% CPU, AMPs 0-2, 4-15 < 5%; 50,247,384 rows all on AMP 3 confirmed via HASHAMP(); composite PI (SHIP_DATE, CARRIER_ID) simulated skew factor 1.05; FACT_SHIPMENT_NEW created with composite PI; FastLoad 50M rows 4h12m; RENAME; post-fix EXPLAIN: all 16 AMPs + PPI partition elimination 273/1,095; elapsed 14m07s; all AMPs 12-15% CPU; 4.5h.” Nationwide Logistics’ operations director can see the 3-hour to 14-minute improvement, the root cause, and the complete work log — without a single phone call asking how many hours the DBA spent on the Teradata issue.

HourTab turns a time-tracker CSV into a public retainer-hours URL your client can bookmark — no login, no portal, no “how many hours do I have left?” emails. Free for one retainer. $9/mo for up to ten.

FAQ: Teradata developer retainers

See also: Oracle DBA retainer · Informatica developer retainer · DB2 developer retainer