Differences between PTI and non-PTI Tables | Teradata Vantage - 17.10 - Differences between PTI and non-PTI Tables - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Time Series Tables and Operations

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1208-171K
Language
English (United States)

PTI tables are optimized to store and retrieve time series data. Time series data typically comes from monitoring sensors that regularly record various data, such as climate or ocean conditions, internal operating status of devices and vehicles, locations of GPS tracking sensors, stock prices, and so forth.

Time series tables are similar to standard, non-PTI Vantage tables, but include a primary time index (PTI) rather than a primary index (PI). Like a PI, a PTI can speed access to frequently queried data, determines how the table row data is distributed among the system AMPs (storage), and determines how the rows are ordered within the table. A properly chosen PTI can help ensure an efficient distribution of data that takes best advantage of the parallelism of Vantage.

Restrictions on PTI Tables

PTI tables support most features and options of standard Vantage tables, with the following exceptions:
  • PTI tables cannot be created as standard primary index (PI) or primary AMP index (PA) tables, and cannot be created as NO PRIMARY INDEX (NoPI) tables..
  • PTI tables cannot have join or hash indexes.
  • PTI tables cannot include row or column partitioning.
  • PTI tables cannot also be temporal tables.
  • PTI tables cannot also be queue tables.
  • PTI tables cannot use load isolation.

For more information about creating standard tables, see Teradata Vantageā„¢ - SQL Data Definition Language Syntax and Examples, B035-1144.