17.05 - NoPI Tables, Column-Partitioned Tables, and WHERE Clause Search Conditions - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
vnq1596660420420.ditamap
dita:ditavalPath
hoy1596145193032.ditaval

NoPI and NoPI column-partitioned tables require a full-table scan to retrieve rows. Column partition elimination or row partition elimination may occur that make the scans less than full-table scans.

Because NoPI and NoPI column-partitioned tables support USIs and NUSIs, consider specifying USIs for conditions designed to retrieve single rows and NUSIs for conditions designed to retrieve row sets. For details, see NoPI Tables and SELECT Statements.

Although you cannot specify join indexes in WHERE clause conditions, the Optimizer uses them if they exist and can be used to avoid a full-table scan. For information about how the Optimizer uses join indexes, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.

Defining join indexes on a NoPI or NoPI column-partitioned table can slow down the loading of rows into the table.