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 to take the second index on the columns.  

Features

• It prevents full table scans
• 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, size in a single table.
• USI takes the only unique value
• NSUI takes duplicate and nulls
• For range-based data VOSI is useful
• It doesn’t affect 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. 

Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore Teradata Training

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

 

Sel* from a party where pcode=20; Party

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

    Sub table content added and removed according to the main table content, which is a burden to the system.

    Sub 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 the party where P-Code between 20 and 33  

Note:

• In the case of USI Sub table will be in other Amp
• In the case of the 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 the party;

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

Value order NUSI

Create index (party code) order by values on the 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)

 

Difference between PI and SI 

FAQ’S

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

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

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

b)USI:- USI on the unique key column

Teradata Interview Questions

Naming conversion And rules

a) As per BIET Names are limited to 30^th character

b) A name can be a combination of A-Z, a-Z,$,#,-

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 ordered so

that this index is useful for range-based query operations.    

Characters literal and Numeric literal

General we used select, insert, etc statement

Select

Party id,

Party name,

‘IT’ as deptCharacter Literal

40 as code Numeric literal

From party;

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

For In-depth knowledge on Teradata click on:

• BTEQ Features in Teradata
• Memory Management in TeraData
• Types of OLAP’s in TeraData
• TPUMP Structure and Process In TeraData
• Teradata Parallel Transporter