INDEXES

                                          PRIMARY INDEXES

                        Indexing is one of the most important features of the Teradata RDBMS. In the Teradata RDBMS, an index is used to define row uniqueness and retrieve data rows, it also can be used to enforce the primary key and unique constraints for a table. 

1)     Primary index is a mandatory index.

2)     Created at the time of Table creation only.

3)     Only one PI for one Table, But PI can be composite upto 64 columns.

4)     If u don't create Index on the table system automatically takes first column as PI.

5)     It is a physical mechanism to assign a row to AMP.

6)     It effects on Data Distribution.

7)     UPI(Unique Primary Index) takes only Unique Values.

8)     NUPI(Non Unique Primary Index) takes Duplicates and Null Values.

9)     PPI for range based queries.

10)PI applys physical mechanism at the time of storage and retrieval.

Note: PI operation is one AMP operation and effects one or many ROWs.

Types of Indexes

1)     Unique Primary Index (UPI)

2)     Non-Unique Primary Index (NUPI)

3)     Partition Primay Index(PPI)

4)     Unique Secondary Index (USI)

5)     Non-Unique Secondary Index (NUSI)

6)     Value Ordered Secondary Index(VOSI)

7)      JoinIndex(JI)

Unique Primary Index:

1.                  It takes only unique values and one null value.

2.                  It distributes data across all AMP's uniformly (or) Randomly (or) evenly , so this distribution is called Uniform Distribution (or) Even distribution.

3.                  One AMP operation and operates only one row

Non Unique Primary Index:

1.                  It takes duplicates and nulls.

2.                  It distributes the data across all AMP's ,it distributes duplicates into same AMP this distribution is less even distribution or uneven distribution.

3.                  This distribution can also be called as 'SKEWED DISTRIBUTION' and less uniform less is 'SKEWNESS'.

4.                  One AMP operation and effects many ROWS.

Partition Primary Index:

1.                  Partition primary index is physically splitting the table into series of sub tables.

2.                   one subtable for every partitioning value.

3.              When a single row is accessed, it looks first at the partitioning value to determine the subtable, then at the primary index to calculate the rowhash for the row(s). 

For example, we have PPI on a MONTH Column, the rows of particular months are all sorted with in the same partition and whenever data is accessed for particular month, it will retrive the data in a faster way.

Note:It helps to avoid full table scans.

SECONDARY INDEXES

1.            Secondary Indexes provide another path to access data.

2.            Teradata allows up to 32 secondary indexes per table.

3.            Keep in mind; row distribution of records does not occur when secondary indexes are defined. 

4.            The value of secondary indexes is that they reside in a subtable and are stored on all AMPs, which is very different from how the primary indexes (part of base table) are stored.

5.            Keep in mind that Secondary Indexes (when defined) do take up additional space. 

6.            Secondary Indexes are frequently used in a WHERE clause.

7.             The Secondary Index can be changed or dropped at any time.

8.             However, because of the overhead for index maintenance, it is recommended that index values should not be frequently changed.

There are two different types of Secondary Indexes

1.Unique Secondary Index (USI)

2.Non-Unique Secondary Index (NUSI).

Unique Secondary Index:

1.            Unique Secondary Indexes are extremely efficient. 

2.            A USI is considered a two-AMP operation. 

3.            One AMP is utilized to access the USI subtable row (in the Secondary Index subtable) that references the actual data row, which resides on the second AMP.

 Non Unique Secondary Index:

1.            A Non-Unique Secondary Index is an All-AMP operation and will usually require a spool file.

2.             Although a NUSI is an All-AMP operation, it is faster than a full table scan.

 Secondary indexes can be useful for:

•       Satisfying complex conditions

•       Processing aggregates

•       Value comparisons

•       Matching character combinations

•       Joining tables

A secondary index can be defined as a Unique Secondary Index (USI) or Non-Unique Secondary Index (NUSI). 

Syntax: 

1.   CREATE INDEX <index name> (<column list>) ON table name;