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.
- 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
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|
Sel * from the party where P-Code between 20 and 33
- 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;
Named index dropping: Drop index<index name>on<table name>
Unnamed index dropping: Drop index(Column name)on<table name>
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
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
Naming conversion And rules
a) As per BIET Names are limited to 30th character
b) A name can be a combination of A-Z, a-Z,$,#,-
c) Object Name is unique in tera data
- 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
‘IT’ as deptCharacter Literal
40 as code Numeric literal
|Party id||Party name||dept||Code|
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