---
title: "Teradata Unique Secondary Index USI"
description: "In Teradata the Unique secondary indexes can thus improve performance by avoiding the overhead of scanning all AMPs. Read more."
url: https://srinimf.com/2014/11/28/teradata-unique-secondary-indexusi/
date: 2014-11-28
modified: 2026-10-07
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2014/11/teradata.png?fit=509%2C339&ssl=1
categories: ["Teradata"]
tags: ["SQL tips and tricks", "Teradata index"]
type: post
lang: en
---

# Teradata Unique Secondary Index USI

The syntax for Unique secondary index is as follows. Also well explained how index is useful in Teradata.

Syntax:

```
CREATE UNIQUE INDEX (department) ON tbl_employee;
```

If a Teradata SQL request uses secondary index values in a WHERE constraint, the optimizer may use the rowID in a secondary index suitable to access the qualifying rows in the data table.

> If a secondary index is used only periodically by certain applications and is not routinely used by most applications, disk space can be saved by creating the index when it is needed and dropping it immediately after use.

A unique secondary index is very efficient, it typically allows access of only *two AMPs*, requires no spool file, and has one row per value, therefore, when a [unique secondary index](http://www.teradatatech.com/?p=815) is used to access a row, two AMPs are involved.

Unique secondary indexes can thus improve performance by avoiding the overhead of scanning all AMPs.

Related[DB2: List of 7 Top Indexes](https://srinimf.com/2010/07/24/db2-indexes/)

*For example,* if a unique secondary index is defined on the department_name column of the Customer_service.department table (assuming that no two departments have the same name), then the following query is processed using two AMPs.

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2014%2F11%2F28%2Fteradata-unique-secondary-indexusi%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2014%2F11%2Fteradata.png%3Ffit%3D509%252C339%26ssl%3D1&description=Teradata%20Unique%20Secondary%20Index%20USI)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2014/11/28/teradata-unique-secondary-indexusi/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2014/11/28/teradata-unique-secondary-indexusi/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2014/11/28/teradata-unique-secondary-indexusi/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2014/11/28/teradata-unique-secondary-indexusi/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20Teradata%20Unique%20Secondary%20Index%20USI&body=https%3A%2F%2Fsrinimf.com%2F2014%2F11%2F28%2Fteradata-unique-secondary-indexusi%2F&share=email)
-

### Like this:

Like Loading…