Teradata Basics
- Teradata - SubQueries
- Teradata - Joins
- Teradata - Primary Index
- Teradata - CASE & COALESCE
- Teradata - Aggregate Functions
- Teradata - Built-in Functions
- Teradata - Date/Time Functions
- Teradata - String Manipulation
- Teradata - SET Operators
- Logical & Conditional Operators
- Teradata - SELECT Statement
- Teradata - Data Manipulation
- Teradata - Tables
- Teradata - Data Types
- Teradata - Relational Concepts
- Teradata - Architecture
- Teradata - Installation
- Teradata - Introduction
Teradata Advanced
- Teradata - BTEQ
- Teradata - FastExport
- Teradata - MultiLoad
- Teradata - FastLoad
- Teradata - Performance Tuning
- Teradata - User Management
- Teradata - Data Protection
- Teradata - OLAP Functions
- Teradata - Partitioned Primary Index
- Teradata - JOIN Strategies
- Teradata - Stored Procedure
- Teradata - Macros
- Teradata - Views
- Teradata - Join Index
- Teradata - Hashing Algorithm
- Teradata - Explain
- Teradata - Compression
- Teradata - Statistics
- Teradata - Secondary Index
- Teradata - Space Concepts
- Teradata - Table Types
Teradata Useful Resources
- Teradata - Discussion
- Teradata - Useful Resources
- Teradata - Quick Guide
- Teradata - Questions & Answers
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
Teradata - Secondary Index
A table can contain only one primary index. More often, you will come across scenarios where the table contains other columns, using which the data is frequently accessed. Teradata will perform full table scan for those queries. Secondary indexes resolve this issue.
Secondary indexes are an alternate path to access the data. There are some differences between the primary index and the secondary index.
Secondary index is not involved in data distribution.
Secondary index values are stored in sub tables. These tables are built in all AMPs.
Secondary indexes are optional.
They can be created during table creation or after a table is created.
They occupy additional space since they build sub-table and they also require maintenance since the sub-tables need to be updated for each new row.
There are two types of secondary indexes −
Unique Secondary Index (USI)
Non-Unique Secondary Index (NUSI)
Unique Secondary Index (USI)
A Unique Secondary Index allows only unique values for the columns defined as USI. Accessing the row by USI is a two amp operation.
Create Unique Secondary Index
The following example creates USI on EmployeeNo column of employee table.
CREATE UNIQUE INDEX(EmployeeNo) on employee;
Non Unique Secondary Index (NUSI)
A Non-Unique Secondary Index allows duppcate values for the columns defined as NUSI. Accessing the row by NUSI is all-amp operation.
Create Non Unique Secondary Index
The following example creates NUSI on FirstName column of employee table.
CREATE INDEX(FirstName) on Employee;Advertisements