DWHPro Insights

How the Number of Rows per Data Block Affects Teradata NUSI Selectivity: A Case Study

By Roland Wenzlofsky ·

How the Number of Rows per Data Block Affects Teradata NUSI Selectivity: A Case Study

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.

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':

Teradata Query Performance Tuning: The DWHPro Guide book cover

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
SELECT * 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

ConceptTeradataSnowflakeMigration Tip
NUSISecondary access path via rowIDsNo secondary index; pruning via metadataDrop NUSIs; rely on micro-partition metadata
Selectivity BasisNumber of base-table data blocksRange of micro-partition valuesLoad data in sorted order to improve pruning
Impact of Row SizeFewer rows per block = higher selectivity thresholdIrrelevant (columnar storage)Data type changes affect storage, not pruning
Covering IndexMay reduce base-table lookupNot applicableUse result caching or materialized views
Tuning ApproachDesign indexes per workloadOptimize clustering keys and reclustering frequencyUse 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.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Follow DWHPro in Google to see our articles more often in Search.

2 thoughts on “How the Number of Rows per Data Block Affects Teradata NUSI Selectivity: A Case Study”

  1. 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

    Reply
  2. 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

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Protected by Security by CleanTalk and CleanTalk Anti-Spam