DWHPro Teradata statistics and join-index labs
Tested 24 October 2026 on Teradata Database 20.00.28.81, BTEQ 20.00.00.11, BTET mode.

Run in a disposable lab database with the named objects unused. Requires permissions
for CREATE TABLE, INSERT, UPDATE, DELETE, CREATE JOIN INDEX, COLLECT STATISTICS,
metadata inspection and DROP, plus access to SYS_CALENDAR.CALENDAR. Submit statements
separately and stop on errors. Do not replace existing objects. Cleanup statements
at the end drop the objects created by each script. No database or account creation
or credentials are included. The scripts were tested independently.

statistics.sql: 20,000 rows, A=19,800 and B=200. Before collecting status statistics,
EXPLAIN estimated 2,000 rows with no confidence; afterward 200 with high confidence.
Both plans used all-AMP scans. After updating 200 rows from A to B, total row count
remained 20,000; B=400. Recollection produced a 400-row high-confidence estimate.
Explicit NO SAMPLE and NO THRESHOLD ensure reproducibility for this small lab.
The 20 percent sample is a syntax example, not a production recommendation.

join-index.sql: 20,000 employees, 100 departments. Department 7 has 200 employees.
The single-table join index changed all-AMP base-table scan to single-AMP index access.
Count/salary: 200/201400.00 before insert, 201/202407.00 after insert,
200/201400.00 after delete. Multi-table join index selected with single-AMP access;
aggregate join index selected with an all-AMP scan of 100 department rows.

These are syntax, result and plan checks, not runtime benchmarks. Plans can differ
with release, configuration, data distribution and system state.

https://www.dwhpro.com/teradata-statistics-3/
https://www.dwhpro.com/teradata-join-index/
