Teradata Index Types

The following types of indexes exists in teradata:

• Primary Index
• Unique Primary Index
• Non-Unique Primary Index
• No Primary Index
• Secondary Index
• Unique Secondary Index
• Non-Unique Secondary Index
• Join Index
• Single-Table Join Index
• Multi-Table Join Index
• Aggregate Join Index
• Sparse Join Index
• Hash Index
• Partitioned Primary Index
• Single Level Partition
• Multi Level Partition

Teradata Object Types

The following types of objects exists in Teradata:

• Tables
• Views
• SQL Stored Procedures
• External Stored Procedures
• Macros
• Triggers
• User-Defined Functions
• User-Defined Methods
• User-Defined Types
• Databases and Users

Teradata Table Types

The following table types exists in Teradata:

• Permanent Table - Once created, remains in the database until it is dropped. It can be shared with different sessions and users.
• Volatile Table - Only the session in which it is created can access the table structure and data. Session ends, table gets dropped.
• Global Temporary Table - Once created, the structure can be shared with different sessions and users but the data remains private to the session who loaded it. Session ends, its private data gets deleted.
• Derived Table - A type of temporary table derived from one or more other tables as the result of a sub-query.
• Queue Table - Permanent tables with timestamp column which indicates when each row was inserted.
• Error Logging Table - Stores errors of an associated permanent table.
• Global Temporary Trace Table - Stores trace output for a particular session duration.
• NoPI Table - Permanent tables that do not have any primary index defined on them.

Logical Structure of Client-Server Interface

Teradata Request Processing

When a SQL query request is submitted to the Teradata Database Server, the following steps occur:

1. Parsing Engine performs the following:
1. Parser - Searches the Request Cache if the request is already there.
2. Syntaxer - Checks the syntax of the request.
3. Resolver - Resolves object names(Database,Table,etc.) to internal identifiers from the Data Dictionary
4. Security - Checks the Data Dictionary for all the necessary privileges.
5. Optimizer
1. Optimize - It determines the most cost effective way to process the request.
2. Lock - Optimizer checks the request whether to place locks on any object.
6. Generator - Transforms the optimized parse tree into plastic steps and caches the steps into the Request Cache if needed.
7. gncApply - Binds in parameterized data if exists and transforms plastic steps into concrete steps.
8. Dispatcher performs the following:
1. Receive - Receives concrete steps
2. Dispatch - Places the first step on the BYNET and tells the BYNET whether this step is for one/several/all AMPs and waits for completion response.
3. Response - Receives completion response from all the expected AMPs and places the next step on the BYNET. This continues until all steps are complete.
2. AMPs - AMPs pick up the steps from the BYNET and processes the step.

Teradata Components

The hardware/physical components of Teradata are:

• Node
• Bynet
• Disk Array Cabinet

The software/logical components of Teradata are:

• Virtual Processors (vProcs)
• Access Module Processor (AMP)
• Parsing Engine (PE)
• Gateway (GTW)
• Node
• Relay Service Gateway (RSG)
• Teradata Virtual Storage (TVS)

Teradata Tools & Utilities

Teradata Active System Management

• Teradata Viewpoint
• Teradata Workload Analyzer (Teradata WA)

Teradata Analyst Pack

• Teradata Index Wizard
• Teradata Statistics Wizard
• Teradata System Emulation Tool (Teradata SET)
• Teradata Visual Explain (Teradata VE)

Teradata Database Management Tools

• Teradata Administrator
• Teradata Query Scheduler (QS)
• Teradata SQL Assistant

Teradata Viewpoint

Teradata Meta Data Services

Storage Management Utilities

• Archive/Recovery (Teradata ARC)
• Backup Application Software