Glossary - Teradata Index Wizard

Teradata Index Wizard User Guide

Teradata Index Wizard
Release Number
English (United States)
Last Update
Product Category
Teradata Tools and Utilities



A special user responsible for allocating resources to a community of users.

access module processor (AMP)

A virtual processor that receives steps from a parsing engine (PE) and performs Teradata Database functions to retrieve or update data. Each AMP is associated with one virtual disk, where the data is stored. An AMP manages only its own virtual disk and not the virtual disk of any other AMP.

aggregate join index

An aggregate join index is a Teradata Database object created using the CREATE JOIN INDEX statement, but specifying one or more columns that are derived from an aggregate expression. Define an aggregate join index as a join index that specifies SUM or COUNT or MIN or MAX aggregate operations. The Join index definition is enhanced to support MIN and MAX aggregate operations from Database version 15.0. No other aggregate functions are permitted in the definition of a join index; however, most of the other simple aggregate statistics can be derived from these using column expressions. You can create aggregate join indexes as either single-table or as multiple join indexes. Aggregate join indexes can also be sparse.


call-level interface (CLI)

A programming interface designed to support SQL access to Teradata Databases from shrink-wrapped application programs. SQL/CLI provides and international standard implementation-independent CLI to access SQL databases. Client-server tools can easily access database through dynamic link libraries. It supports and encourages a rich set of client-server tools.


In the relational model of Teradata SQL, databases consist of one or more tables. In turn, each table consists of fields, organized into one or more columns by zero or more rows. All of the fields of a given column share the same attributes.


This is the outlay of database resources used by a given query.


data definition

The statements and facilities that manipulate Teradata Database structures and the Data Dictionary information kept about these structures. These statements include CREATE, DROP, ALTER, and MODIFY.

data definition language (DDL)

In Teradata SQL, the statements and facilities that manipulate Teradata Database structures (such as CREATE, MODIFY, DROP, GRANT, REVOKE, and GIVE) and the dictionary information kept about those structures. In the typical, pre-relational data management system, data definition and data manipulation facilities are separated, and the data definition facilities are less flexible and more difficult to use than in a relational system.

data dictionary

In the Teradata Database, the information automatically maintained about all tables, views, macros, databases, and users known to the Teradata Database system. It includes information about ownership, space allocation, accounting, and privilege relationships between those objects. Data Dictionary information is updated automatically during the processing of Teradata SQL data definition statements. It is used by the parser to obtain information needed to process all Teradata SQL statements.

data manipulation language (DML)

In Teradata SQL, the statements and facilities that manipulate or change the information content of the database. These statements include INSERT, UPDATE, and DELETE.

data source name (DSN)

Through a data source name, th0.

e ODBC driver for Teradata maps a logical name to the necessary software components and any additional information required for accessing the Teradata Database. The user chooses the DSN. The DSN should be a name that reflects the type of data or system it represents.


A related set of tables that share a common space allocation and owner. A collection of objects that provide a logical grouping for information. The objects include, tables, views, macros, triggers, and stored procedures.

database query log (DBQL)

A historical record of SQL statements that are run in Teradata over a period of time.


Acronym for database administrator

domain name

A group of computers whose hostnames (the unique name by which a computer is known on a network) share a common suffix, that is the domain name.



This refers to extracting or transferring system information from the tables and views of a given Teradata Database and saving it so it can be manipulated or pulled into another system.



The basic unit of information stored in the Teradata Database. A field is either null, or has a single numeric or string value.


The number of times a particular statement exists within a workload.


global join index

Also known as a global index. It is a single-table join index, except for one important difference: each global index row carries a pointer to its base table that can be used as an alternate way to access base table rows. Using a join index for base table access is referred to as partial covering because such a join index only partially covers the query.

globally distributed objects (GDO)

A data structure that is shared by all of the virtual processors in the Teradata Database configuration.

graphical user interface (GUI)

The use of pictures rather than just words to represent the input and output of a program. A program with a GUI runs under a Windows operating system. The GUI displays certain icons, buttons, dialog boxes in its windows on the screen and the user controls it by moving a pointer on the screen (typically controlled by a mouse) and selecting certain objects by pressing buttons on the mouse. This contrasts with a command line interface where communication is by exchange of strings of text.


hash-ordered secondary index

a secondary index whose rows are ordered by hash.



This refers to the process of pulling system information into a program. To add system information from an external source to another system. The system receiving the data must support the internal format or structure of the data.

index analysis

The process Teradata Index Wizard uses analyze a set SQL statements to find indexes to recommend.

interface processor (IFP)

Used to manage the dialog between the Teradata Database and the host. Its components consist of session control, client interface, the parser, the dispatcher, and the BYNET. One type of IFP is a communications processor (COP). A COP contains a gateway process for communicating with workstations via a network.

internet protocol (IP)

Data transmission standard; the standard that controls the routing and structure of data transmitted over the Internet.



A select operation that combines information from two or more tables to produce a result.


kilobyte (KB)

1000 bytes.



A record of events. A file that records events. Many programs produce log files. Often you will look at a log file to determine what is happening when problems occur. Log files have the extension “.log”.



A set of Teradata SQL statements stored by the Teradata Database and executed by a single EXECUTE statement. Each macro execution is implicitly treated as a transaction.



A word supplied by the user that refers to an object, such as a column, database, macro, table, user, or view.

non unique secondary index

One of two types of secondary indexes. A secondary index may be specified at table creation or at any time during the life of the table. It may consist of up to 16 columns. To get the benefit of the index, the query has to specify a value for all columns in the secondary index. A non unique secondary index is usually specified in order to prevent full-table scans.


The absence of a value for a field.



In object-oriented programming, a unique instance of a data structure defined according to the template provided by its class. Each object has its own values for the variables belonging to its class and can respond to the messages, or methods, defined by its class.

object definition

Shows the details of the structure and instances of the objects used by a given query. Object definitions are used to create tables, views, macros, triggers, join indexes, and stored procedures in a database.

open database connectivity (ODBC)

Under ODBC, drivers are used to connect applications with databases. The ODBC driver processes ODBC calls from an application, but passes SQL requests to the Teradata Database for processing. See “Connecting Through ODBC” on page 36 for more information.



A variable name in a macro for which an argument value is substituted when the macro is executed.


A program executing in a parsing engine that translates Teradata SQL statements entered by a user into the steps that accomplish the user’s intensions.

parsing engine (PE)

An instance (virtual processor) of the database management session control, parsing, and dispatching processes and their data context (caches).

production system

A Teradata Database used in a live environment at a customer site. A system that is actively used for day to day business operations. This differs from a test or development system that is used to create new queries or test new features before using them on the production system.



A Teradata SQL statement, such as a SELECT statement.

query capture database (QCD)

A database of relational tables that store the steps of any query plan captured by the Query Capture Facility (QCF). See “Defining a Workload From QCD Statements” on page 60 for more information.

query capture facility (QCF)

Provides a method to capture and store the steps from any query plan in a set of predefined relational tables called the Query Capture Database (QCD).


random AMP sample (RAS)

An arbitrary sample from an Access Module Processor (AMP). These are samples of the tables in a query or all of the tables in a given database.


In host software, a message sent from an application program to the Teradata Database.


The information returned to the user to satisfy a request made of the Teradata Database.


The fields that represent one entry under each column in a table. The row is the smallest unit of information operated on by data manipulation statements.



In client software, a logical connection between an application program on a host and the Teradata Database. It permits the application program to send one request to and receive one response from the Teradata Database at a time.

simple join index

The primary function of a simple join index is to provide the Optimizer with a cost-effective means for satisfying any query that specifies a frequently performed join operation. The simple join index permits you to define a permanent prejoin table without violating the normalization of the database schema.


A request for processing by the Teradata Database that consists of a keyword verb, optional phrases, and operands. It is processed as a single entity.


These are the details of the processes used to collect, analyze, and transform the database objects used by a given query.

stored procedures

Combinations of procedural and non-procedural statements run using a single call statement.

structured query language (SQL)

SQL is a standardized query language for requesting information from a database. SQL consists of a set of facilities for defining, manipulating, and controlling data in a relational database.



A two-dimensional structure made up of one or more columns with zero or more rows that consist of fields of related information. See also database.

target system

A Teradata Database from which you export Optimizer-specific information for modeling on a second, test system.

target level emulation (TLE)

Permits you to emulate a target environment (target system) by capturing system-level information from that environment. The captured information is stored in the relational table SystemFE.Opt_Cost_Table. The information in this table can be used on a test system with the appropriate column and indexes to make the Optimizer generate query plans as if it were operating in the target system rather than the test system.

Teradata Director Program Identifier (TDPID)

The name of the Teradata Database being monitored. Also used to establish a preprocessor connection to the Teradata Database.

Teradata DQM

Acronym for Teradata Dynamic Query Manager

Teradata SET

Acronym for Teradata System Emulation Tool.


A Teradata provides information that enables decision-makers within an organization to manage customer relationships efficiently. It is about integrating advanced decision support with day-to-day, even minute-to-minute decision making that increases quality which encourages customer loyalty and thus secures an organization's bottom line.

test system

A Teradata Database where you want to import Optimizer-specific information to emulate a target system and create new queries or test new features.


Acronym for Teradata Index Wizard


One or more Teradata SQL statements associated with a table and executed when specified conditions are met.


Unicode®, UTF-8, and UTF-16 character sets

UTF-8 and UTF-16 are two of the standard ways of encoding Unicode® character data. The Teradata Database supports UTF-8 and UTF-16 client character sets. The UTF-8 client character set supports UTF-8 encoding. The Teradata Database supports UTF-8 characters that can consist of from one to three bytes. The UTF-16 client character set supports UTF-16 encoding. The Teradata Database supports the Unicode® 6.0 standard, where each defined character requires exactly 16 bits. There are restrictions imposed by the Teradata Database on using the UTF-8 and UTF-16 character sets. For details, see International Character Set Support (B035-1125).

unique secondary index

One of two types of secondary indexes. A secondary index may be specified at table creation or at any time during the life of the table. It may consist of up to 16 columns. To get the benefit of the index, the query has to specify a value for all columns in the secondary index. A unique secondary index has two purposes: It can speed up access to a row which otherwise might require a full table scan without having to reply on the primary index, and it can be used to enforce uniqueness of a column or set of columns.


A database associated with a person who uses the Teradata Database. The database stores the person’s private information and accesses other Teradata Databases.


value ordered secondary index

A non-unique secondary index can be value ordered which means the non unique secondary index can be sorted on the key values themselves rather than on the corresponding hash codes. This is useful for range queries where only a portion of the index subtable will be accessed. With a value-ordered non unique secondary index, only those blocks in the non unique secondary index subtable that are within the range are scanned. It must be a number value, up to 4 bytes, versus a longer character column. DATE is the most commonly used data type. The actual data value is stored as part of the non unique secondary index structure.


An alternate way of organizing and presenting information in the Teradata Database. A view, like a table, has rows and columns. However, the rows and columns of a view are not directly stored by the Teradata Database. They are derived from the rows and columns of tables (or other views) whenever the view is referenced.



A workload is a set of SQL statements executed against a database. A single SQL statement is also a valid workload.