Why Teradata use sub-table for Secondary Index


Why Teradata use sub-table for Secondary Index

Hi Friends,

I am new to Teradata, I was going through various slides and documents to enrich my Teradata skills. I am confused that why teradata using sub-table concept for secondary index. Also few more queries raises from here:

1. Does Primary & Secondary Index or conceptually same?
2. If conceptually same, why teradata use sub-table?

Apart from below difference between primary index and secondary index, I need more stuff.

1. primary index cannot create after table creation, whereas secondary index can be created dynamically.
2. primary index is 1 AMP operation, secondary index is 2 AMP operation and non unique secondary index is ALL AMP operation.

Hope whoever read this message would able understand what i want to know.

Thx in Advance

Re: Why Teradata use sub-table for Secondary Index

Dear Anand,

The Primary and Secondary Index are both different ways of retrieving data. However, the secondary index is slightly slower because it has to take an extra step to find the rows you are looking for (the extra step is the stopoff at the secondary index subtable). The subtable only has 3 things within it: The secondary index value, the secondary index row id, and the base row ID. The secondary index is great when you constantly run queries on a table using a non-primary-indexed column in the where clause.

The subtable is used to find the row id for the primary index. So what happens is that the seconday index value is hashed and sent to the hash map to find the proper AMP. Once it finds the AMP where the secondary index row ID resides, it will find that ID in the subtable, see where the base row ID is pointing to, then Teradata will go to that AMP to get the row. This is why it's considered a two step operation.

Something to note about NUSIs: While they are all AMP operations, they are not full table scans. The secondary index subtable is a lot smaller than the actual base table and can be scanned through at a much quicker rate.

I hope this helps!

Re: Why Teradata use sub-table for Secondary Index

hi anand,
PI and SI are not we could say conceptually same. Becoz, PI is primarily used for Data Distribution( the way, TD stores the data), and SIdex never used for Data Distribution.

there r many minor n major diffs between them of this sort.
pls refer the TD docs for more clear understanding.

Re: Why Teradata use sub-table for Secondary Index

As usual, Tom is on the mark here.

The important thing (I think) to take away from this is that hashing on the secondiary index doesn't get you the rowid for the record you are looking for. It gets you the rowid for the SI record in the sub-table.

You have to retrieve that record to get the rowid for the record you really want.

Since secondary indexes are stored in a table, they allow for range based selection of data. Something a PI doesn't support.

Re: Why Teradata use sub-table for Secondary Index

When primary index is there why do we go for secondary index?

Re: Why Teradata use sub-table for Secondary Index

What is meant by join index?? can yu explain with an example?