• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

Secondary Index in TeraData

Secondary Index

Real time usage

Other than primary index columns if we are using in the where clause, To get fast retrieval data take second index on the columns.

 

Features

It prevents full table scan

Basically for getting fast Retrieval of data, this is record mended

We can create and drop SI at any time (Creation can be at table creation/after table creation)

We can create Max 32, SI, a size in single table.

USI takes only unique value

NSUI takes duplicate and nulls

For range based data VOSI is useful

It doesn’t effect data distribution.

It uses a sub table mechanism as part of its operations

Each SI can be composite till 64 columns

It is two or many Amp operation. 

At TekSlate, we offer resources that help you in learning various IT courses. We avail both written
 material and demo video tutorials. To gain in-depth knowledge and be on par with practical
 experience, then explore Teradata Training Online.

S.I Sub table Mechanism[Storage and retrieval Architecture]

Screenshot_65

 

Sel* from party where pcode=20;

Party

PID PNM PCODE
1 X 100
2 Y 20
3 Z 33
4 K 20

 

 

2Sub table content added and remove according to the main table content, which is burden to system

2Sub table may in the same Amp(or) it may be indifferent Amp

 

S.I ROW ID Column value P.I row ID
  202033100  

Sel * from party where P Code between 20 and 33

 

Note

In case of USI Sub table will be in other Amp

In case of NUSI Sub table will be in the same Amp.

 

Creation and dropping

AT table creation

USI:_ Create table<table name>unique index(column name)

NUSI:- Create table<table name> index(column name)

 

After table creation

USI:_ Create unique index idx- party CD(party code) on party;

NUSI:- Create index idx- party CD(party code) on party

 

Value order NUSI

Create index (party code) order by values on party;

 

Dropping indexes

Named index dropping:_ Drop index<index name>on<table name>

Unnamed index dropping:_ Drop index(Column name)on<table name>

 

 

Final Example

Create set table party test fall back (party id integer, party name varchar(30)upper case, party income integer check(party income>5000), party code integer) unique primary index(party id)unique index(party code)

Screenshot_67

 

Difference between PI and SI 

FAQ’S

2There is table with primary key and unique key is available what types of index the system create by default?

Ans:- As primary key give high priority , it takes UPI on primary key and USI on unique

a) UPI:- UPI on primary key column and unique key column as USI

b)USI:- USI on unique key column

Learn more about Teradata Interview Questions in this blog post.

Naming conversion And rules

  1. a) As per BIET Names are limited to 30th character
  2. b) A name can be combination of A-Z, a-Z,$,#,-
  3. c) Object Name is unique in tera data

USI

  1. Takes unique values, generally sub table stoves in other Amp’s

 

[Two Amp operations and returns only one row]

VOSI – In this situation instated of secondary index row ID/row hash sequence actual values will be order so that this index is useful for range based query operations.

Screenshot_66

 

 

Characters literal and Numeric literal

General we used select, insert etc statement

Select

Party id,

Party name,

‘IT’ as dept2Character Literal

40 as code2 Numeric literal

From party;

Party id Party name dept Code
1 Vinay IT 40
2. Madhu IT 40
3. Meera IT 40

 

For Indepth knowledge on Teradata click on:

Summary
Review Date
Reviewed Item
Secondary Index in TeraData
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Secondary Index in TeraData"

Leave a Message

Your email address will not be published. Required fields are marked *

Site Disclaimer, Copyright © 2016 - All Rights Reserved.