The historical threshold and performance results below have not been reproduced on the current release. A 20,000-row functional check confirmed that the former decimal literals 19.332 and 19.333 both selected only integer keys through 19, while 19332 selected keys through 19,332. The SQL cutoffs have been corrected.
Part of the Teradata Physical Design and Data Modelling guide.
Teradata NUSI Selectivity and Data-Block Density
The goal of this article is to show how the number of rows per base-table data block impacts the selectivity threshold for Non-Unique Secondary Indexes (NUSI) in Teradata.
Understanding this correlation is critical when analyzing query plans and tuning indexing strategies.
The number of qualifying rows that make a NUSI efficient depends not only on data distribution and statistics, but also on how many rows fit into a single data block.
Example Setup
The historical test table NUSI_SELECTIVITY contained about 270,000 rows. PK was used as its primary index; that choice alone does not guarantee even AMP distribution. COL_A was used for qualification.
CREATE MULTISET TABLE INDEXING.NUSI_SELECTIVITY
(
PK INTEGER,
COL_A CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC
) PRIMARY INDEX ( PK );
SELECT COUNT(*) FROM INDEXING.NUSI_SELECTIVITY;
-- 269,378
We then created a Non-Unique Secondary Index (NUSI) on COL_A:
CREATE INDEX (COL_A) ON INDEXING.NUSI_SELECTIVITY;
The uncompressed column payload is 14 bytes in this LATIN example: 4 for INTEGER and 10 for CHAR(10). Physical row size also includes row metadata and may be affected by compression. The original decimal cutoffs were corrected below to integer values 19332 and 19333.
INSERT INTO INDEXING.NUSI_SELECTIVITY
SELECT
PK,
CASE WHEN PK <= 19332 THEN 'A' ELSE 'B' END
FROM
INDEXING.SOME_TABLE;
To provide the optimizer with column demographics, the historical test collected statistics. This does not guarantee a fixed plan across releases, workloads or configurations.
COLLECT STATS ON INDEXING.NUSI_SELECTIVITY COLUMN(COL_A);
Tip: Always collect statistics on indexed columns when analyzing NUSI thresholds; random AMP sampling can otherwise distort the plan.
When Teradata Uses the NUSI
We now query all rows with COL_A = 'A':
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookSELECT * FROM INDEXING.NUSI_SELECTIVITY WHERE COL_A = 'A';
At 19 332 qualifying rows (~7.1 %), the optimizer still uses the NUSI:
EXPLAIN SELECT * FROM INDEXING.NUSI_SELECTIVITY WHERE COL_A = 'A';
(Plan excerpt shows “by way of index #4”)
When we slightly increase the selection to 19 333 rows, the optimizer switches to a full-table scan (FTS) because it now estimates that scanning all base-table blocks is cheaper than following NUSI rowIDs.
DELETE FROM INDEXING.NUSI_SELECTIVITY;
INSERT INTO INDEXING.NUSI_SELECTIVITY
SELECT
PK,
CASE WHEN PK <= 19333 THEN 'A' ELSE 'B' END
FROM
INDEXING.SOME_TABLE;
COLLECT STATS ON INDEXING.NUSI_SELECTIVITY COLUMN(COL_A);
EXPLAIN SELECT * FROM INDEXING.NUSI_SELECTIVITY WHERE COL_A = 'A';
Why the Optimizer Switches
Teradata reads and moves data blocks, not individual rows.
When each qualifying row resides in a different data block, NUSI access becomes as costly as scanning the table once sequentially.
For non-covering NUSI access, the optimizer compares index work and qualifying base-block reads with alternative paths. A covering index can return requested data without fetching base-table blocks, so the cost comparison differs.
The reported switch between 19,332 and 19,333 qualifying rows is a historical observation, not evidence of a universal one-row boundary or a measured extra physical block read.
Selectivity Calculation
The selectivity of this query is:
19 332 ÷ 269 378 = 7.1 %
The roughly 7% crossover belongs to this historical setup. Data distribution, index coverage, row/block sizes, compression, statistics and optimizer release can all change the threshold.
Changing Row Size and Block Density
The historical copy below also changes the primary index from nonunique to unique. This confounds a row-width-only comparison. A new controlled test must keep the other table options and data generation constant.
CREATE MULTISET TABLE INDEXING.NUSI_SELECTIVITY_COPY, NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
PK INTEGER,
COL_A CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC
)
UNIQUE PRIMARY INDEX ( PK );
The longer rows reduce the number of rows per data block.
Repeating the test showed that 24 136 rows (~9 %) could still use the NUSI before the optimizer switched to FTS.
Observation:
Fewer rows per block → higher selectivity threshold → NUSI stays useful longer.
Changing the character set from LATIN to UNICODE (2 bytes per character) would have a similar effect.
Even schema design choices like data type or encoding can alter the optimizer’s access strategy.
The Covering-NUSI Surprise
Finally, we tested whether selecting only the indexed column (COL_A)—making the NUSI covering—would change the decision:
EXPLAIN SELECT COL_A FROM INDEXING.NUSI_SELECTIVITY_COPY WHERE COL_A = 'A';
Surprisingly, even in this case, the optimizer still performed a full-table scan under Teradata 14.10.
The reported covering-index plan was observed under 14.10. Do not generalize it to later releases or assume a covering NUSI must fetch base-table blocks. Check the current plan and compare equivalent workloads.
Key Takeaways for Teradata
- Teradata’s optimizer decides between NUSI and FTS based on data-block I/O, not row count.
- The number of rows per block (driven by row length and compression) shifts the threshold.
- Common rules such as “use NUSI below 10 % selectivity” are oversimplified.
- A small schema change can alter the optimizer’s decision path.
The Snowflake Perspective
Snowflake standard tables do not have Teradata-style NUSIs. They use micro-partition metadata and may use other access optimizations. Hybrid tables have different indexing capabilities, so a blanket statement about all Snowflake tables is inaccurate.
- Each micro-partition stores min/max values and null counts for every column.
- For predicates supported by metadata pruning, Snowflake can skip micro-partitions whose metadata rules out a match. Not every WHERE expression supports equally effective pruning.
- Pruning efficiency depends on how well the data was loaded and clustered, not on row length or block density.
- Re-clustering can be triggered automatically using the Clustering Service to restore optimal pruning.
NUSI selectivity and micro-partition pruning both concern reducing work, but they are different access mechanisms and their percentages are not interchangeable.
Migration Guidelines: Teradata → Snowflake
| Concept | Teradata | Snowflake | Migration Tip |
|---|---|---|---|
| NUSI | Secondary access path via rowIDs | No secondary index; pruning via metadata | Drop NUSIs; rely on micro-partition metadata |
| Selectivity Basis | Number of base-table data blocks | Range of micro-partition values | Load data in sorted order to improve pruning |
| Impact of Row Size | Fewer rows per block = higher selectivity threshold | Irrelevant (columnar storage) | Data type changes affect storage, not pruning |
| Covering Index | May reduce base-table lookup | Not applicable | Use result caching or materialized views |
| Tuning Approach | Design indexes per workload | Optimize clustering keys and reclustering frequency | Use SYSTEM$CLUSTERING_INFORMATION to measure efficiency |
Migration changes physical-design choices. For Snowflake standard tables, evaluate pruning, clustering and applicable search optimization; do not assume optimal access arises automatically or that a Teradata threshold carries over.
Summary
- Teradata NUSI performance depends on how many data blocks contain qualifying rows.
- Row length and block density directly influence optimizer selectivity thresholds.
- Snowflake eliminates NUSIs but introduces a new tuning dimension: micro-partition clustering.
- Understanding both helps translate physical design strategies from on-prem Teradata to the cloud.
Don’t forget to watch our video course on Teradata indexing techniques.
Also, check out:
The Teradata USI compendium
The NUSI compendium: Teradata’s Secondary Index
Need help with query performance or platform costs?
Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book
Thank you very much. It is a big honor to hear this from Tera-Tom himself 😉
I am a big fan of your Teradata books I remember, the first one I bought was “Tera-Tom on Database Administration” for V2R6 which helped me a lot to better understand the Teradata architecture.
Thanks again,
Roland
Roland,
Another brilliant article bringing to light important information to know when one wants to become a Teradata expert. Thank you again for another home run!
Tom Coffing