Guidelines for Choosing a Primary Time Index | Teradata Vantage - Guidelines for Choosing a Primary Time Index - Advanced SQL Engine - Teradata Database

Time Series Tables and Operations

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
zzg1600277315070.ditamap
dita:ditavalPath
zzg1600277315070.ditaval
dita:id
B035-1208
lifecycle
previous
Product Category
Teradata Vantageā„¢
The goals in selecting a primary time index are the same as for selecting a regular primary index for non-PTI tables:
  • Choose a PTI that will identify each row to result in the most even distribution of time series data among the AMPs in the map used by the table. Use the table below to help determine what to include in your PTI.
  • Choose a PTI that includes columns that are frequently queried, to speed data access for those queries.
These goals are discussed in more detail for primary indexes in Teradata Vantageā„¢ - Database Design, B035-1094.

All PTIs use the the timestamp data from the time series data, and require the timecode_data_type in the PTI definition. The following general guidelines can help you determine what else to specify in your PTI definition, based on the general nature of the time series data. In all cases, you can optionally include a timezero_date in your PTI definition.

Time Series Characteristics Include in PTI Basis for Data Distribution to AMPs
Single continuous long time series

Example: A building monitoring system collects sensor data for a single building on a 7/24 continuous basis.

timebucket_duration Timebucket value
Multiple continuous, long time series

Example: Ongoing oceanic and atmospheric monitoring, such as ocean buoy sensor data, where there are thousands of buoys, and data is received from each buoy over a long period of time.

timebucket_duration and column_list

Choose columns likely to be in common queries of the PTI table.

Timebucket value, and values of columns included in PTI
Short, static time series

Example:

Airline flight information, where each flight has an known departure time and arrival time within a short and static time frame.

Other examples include scanning data from ultrasound and CAT scan imaging, and electron microscope crystal or cell structure data.

column_list

Choose columns likely to be in common queries of the PTI table.

Values of columns included in PTI